With the help of the ARRAYFORMULA function, we can retrieve multiple values using the VLOOKUP function in Google Sheets.
There are two distinct scenarios when VLOOKUP returns multiple values in combination with ARRAYFORMULA:
- Using Multiple Search Keys: You can look up two or more values simultaneously within a column.
- Using Multiple Column Indexes: You can retrieve values from specific cells or the entire row of the matching entry.
Using Multiple Search Keys and Retrieving Multiple Result Values with VLOOKUP
In the following example, I have sample data in one of my Google Sheets in the range B3:C8. Here, B3:B8 contains player names, and C3:C8 contains their ages.
In this sample data, we can use the following VLOOKUP formula to return the age of a single person whose name is specified in cell E3:
=VLOOKUP(E3, B3:C8, 2, 0)This formula follows the syntax:
VLOOKUP(search_key, range, index, [is_sorted])Where:
search_key: E3range: B3:C8index: 2is_sorted: 0. It represents an exact match of thesearch_key(you can specify FALSE as well)
To retrieve two values, the ages of multiple persons in our context, specify their names in cells E3:E4 and use the following formula:
=ARRAYFORMULA(VLOOKUP(E3:E4, B3:C8, 2, 0))
Note: If you hardcode the criteria instead of specifying them as E3:E4, use the VSTACK function as shown below:
VSTACK(search_key1, search_key2, ...)This example demonstrates how to retrieve multiple values using multiple search keys in VLOOKUP.
Here is a specific tutorial that covers this topic: How to Use VLOOKUP to Return an Array Result in Google Sheets
Using VLOOKUP to Retrieve All Values from the Matching Row (Using Multiple Column Indexes)
Sample Data:
In the following example, I have product names in column A and sales from the first half of the year 2017 in columns B to G. The actual range for lookup is A3:G6. Please refer to the screenshot provided below.
Lookup:
Enter the product name to search in cell A8.
To search it and return sales values from Jan to Jun, you can use the following VLOOKUP formula in cell B8.
=ARRAYFORMULA(VLOOKUP(A8, A3:G6, {2, 3, 4, 5, 6, 7}, 0))This represents the second scenario where we retrieve multiple values using the VLOOKUP function in Google Sheets.
Formula Explanation
In this example, the search key used is from cell A8, which is utilized to search for availability in the first column within the range A3:G6.
The match is found in cell A4, and subsequently, the lookup values from cell B4:G4 in the same row are returned.
This demonstrates the use of the VLOOKUP formula to return all or selected values from a row based on a match in the first column of that row.
How do these multiple values (multiple-column indexes) in VLOOKUP work?
When you want to return a single value using VLOOKUP, you can use a formula similar to the one below:
=VLOOKUP("PRODUCT 2", A3:G6, 2, FALSE)The above VLOOKUP formula will find the search key in cell A4 and return the value from the second column in the same row.
This is an example of the use of the standard VLOOKUP formula in Google Sheets.
When you want to return multiple values using VLOOKUP, you should use multiple column (index) numbers within curly braces as shown below:
{2, 3, 4}This creates an array that can return multiple column values in Google Sheets, specifically values from columns 2, 3, and 4. However, do not use the formula as follows:
=VLOOKUP("PRODUCT 2", A3:G6, {2, 3, 4}, FALSE)We have utilized curly braces to generate an array within the VLOOKUP formula. However, VLOOKUP is not an array formula by default.
Therefore, we should additionally use an ARRAYFORMULA with the above formula. The final formula will be as follows:
=ARRAYFORMULA(VLOOKUP("PRODUCT 2", A3:G6, {2, 3, 4}, FALSE))This way, you can return multiple values using VLOOKUP in Google Sheets.
Great discussion. How would this work on using the Importrange function?
How would it be written in my case, where I have up to 30 columns to search?
Hi, JB9,
That might be possible. I require a sample Sheet to offer any possible solution.
Hello, I have read lots of your guides, but I am lost.
I have two sheets.
I want to bring all the rows that contain the matching word from Sheet 2 to Sheet1.
So if I have in Sheet 2:
Column 1 | Column2
Shakira | Blue
Shakira | Orange
Britney | Pink
Britney | Red
Shakira | Yellow
I want to have it in Sheet 1.
Column 1 | Column 2
Shakira | Blue, Orange, Yellow.
Britney | Pink, Red
Hi, Felipe Andres,
You can use this formula in Sheet1.
=textjoin(", ",true,filter(Sheet2!B:B,Sheet2!A:A=A2))Then drag it (copy-paste) down.
Hi Prashanth,
Thanks so much for the quick reply! This is EXACTLY what I was looking for – I really appreciate your help!!!
Also – I realized I left a duplicate comment on your thread. Sorry about that, and feel free to ignore it.
Isabel
Hello,
Is it possible for the ‘Filter’ function to ignore parameters when they are blank?
I’m trying to create a tool that returns a list of names that matches two criteria: State of residence, and Enrollment in a program (see example link below)
Thanks so much in advance!
Hi, Isabel,
Yes! We can do that in multiple ways in Google Sheets. I have copied your sheet as its sharing setting doesn’t allow me to put my formula.
I’ll leave the example ASAP below. Please stay tuned!
Update:
Please find my related tutorial here – IF Statement within Filter Function in Google Sheets.
Based on that, you can use the following formula.
=filter('Source Data'!A2:A,if(B2="",T('Source Data'!B2:B)<>"",'Source Data'!B2:B=B2),if(B3="",T('Source Data'!C2:C)<>"",'Source Data'!C2:C=B3))Other Formula Suggestions:
Option 1:
=filter('Source Data'!A2:A,regexmatch('Source Data'!B2:B&'Source Data'!C2:C,textjoin("",true,B2:B3)))Option 2:
=filter('Source Data'!A2:A18,if(B2="",'Source Data'!B2:B18<>"",'Source Data'!B2:B18=B2),if(B3="",'Source Data'!C2:C18<>"",'Source Data'!C2:C18=B3))Hello,
I’m trying to modify your formula to return data for multiple lines.
There are duplicate entries for the primary cell being used in the Vlookup. Would it be possible to use a similar formula to do this?
For example – if “Richard” showed up multiple times on the primary column, and I wanted to see all entries in the column range that the Vlookup is searching?
Hi, James,
You can try the Filter function.
For further assistance, please make a demo sheet and share it below. I won’t publish the address of the Sheet.
Hi there. Thanks for this very helpful article. I have a question, however: how to proceed if I wish to return multiple columns over many rows of data?
Example:
ArrayFormula(vlookup(E3:E4,B3:C8,{2,3,4},0))I wish to use a similar lookup in my scenario but cannot seem to get this to work.
Hi, Daniel Orr,
The above formula won’t work as there are only two columns in the ‘range’ and that should be specified as
{1,2}, not as{2,3,4}.So this Vlookup formula may work.
=ARRAYFORMULA(vlookup(E3:E4,B3:C8,{1,2},0))