Have you ever thought that using the Vlookup function in Google Sheets only allows you to return a single value? Well, you’re in for a surprise! Contrary to popular belief, you can actually use Vlookup to return multiple values. How, you ask? By harnessing the power of arrays in Google Sheets!
Using Multiple Search Keys
One way to achieve multiple value outputs with Vlookup is by using multiple search keys. By incorporating more than one search key in the Vlookup formula, you can retrieve more than one value as the output. It’s as simple as that!
Check out this example formula and output to get a better understanding:
=ArrayFormula(vlookup(E3:E4,B3:C8,2,0))
If you want to dive deeper into this topic, you can find more details on Crawlan.com in our article: How to Use Vlookup to Return An Array Result in Google Sheets.
Using Multiple Index Columns
Let’s move on to the second scenario where you can use Vlookup to get multiple values in the output by utilizing multiple index columns. Here’s an example to help illustrate the concept:
Suppose we have a search key, “Product 2,” and we want to find its availability in the first column within the range A3:G6. Once the match is found in cell A4, the Vlookup formula will return the corresponding values in cells B4:G4. This means you can use the Vlookup formula to return all or selected values from a row based on a match in the first column.
But how does this magic of multiple values (multiple-column indexes) in Vlookup work? Let’s find out!
In Google Sheets, How to Return Multiple Values Using Vlookup (All the Values from the Matching Row)
When you only need to return a single value using Vlookup, you can use a formula like this:
=VLOOKUP("PRODUCT 2",A3:G6,2,FALSE)In the above example, the Vlookup formula searches for the key “Product 2” in cell A4 and returns the value from the second column in the same row. This is a typical usage of the normal Vlookup formula in Google Sheets.
But what if you want to return multiple values using Vlookup in Google Sheets? In that case, you need to employ multiple column (index) numbers within curly braces, like this:
{2,3,4}By using curly braces, you create an array that can return values from columns 2, 3, and 4. However, keep in mind that you should not use the formula as shown below:
=VLOOKUP("PRODUCT 2",A3:G6,{2,3,4},FALSE)To make Vlookup treat the formula as an array, you should use an ARRAYFORMULA in addition to the Vlookup formula. The final formula will look like this:
=ARRAYFORMULA(VLOOKUP("PRODUCT 2",A3:G6,{2,3,4},FALSE))And there you have it! You can now return multiple values using Vlookup in Google Sheets. Make sure to give it a try and see the magic unfold.
For more insightful tutorials and tips, be sure to visit Crawlan.com. And don’t forget to check out our Index Match tutorial, which is often considered superior to Vlookup in terms of functionality.
Happy spreadsheet-ing!
