You may think that using Vlookup in Google Sheets, you can only return a single value. But on the contrary to that, you can use Vlookup to return multiple values. To precise, Multiple values using Vlookup is possible with the help of an Array in Google Doc Spreadsheets.
Here is an example.
In this example, I have used the search key in A8, i.e. “Product 2” to lookup first column in the range A3:G6. It finds the match in cell A4, and subsequently returns the look up values in cell B4:G4 in the same row. How this multiple values in Vlookup work?
In Google Sheets How to Return Multiple Values Using Vlookup
When you want to return a single value using Vlookup, you can use the formula as below.
The above Vlookup formula would find the search key “Product 2” in Cell A4 and return the value in the second column in the same row. This’s example to a normal lookup function.
When you want to return Multiple Values Using Vlookup in Google Sheets, you should place the column number within curly braces as below.
This creates an array and it can return multiple column values in Google Sheets. That means values in the column 2, 3 and 4. But you can’t use the formula as below.
You May Also Like: How to Use VLOOKUP with Multiple Criteria in Google Sheets
We have used Curly Brackets to generate an array within VLookup formula. But VLookup is not an Array Formula by default. So we should additionally use an ARRAYFORMULA with the above formula. The final formula will be as below.
Note: Retype the curly braces if you copy this Vlookup formula
This way you can return multiple values using Vlookup in Google Sheets. Don’t forget to check our Index Match tutorial which is better than Vlookup in functionality.