Often, we need to combine two or more criteria to look for certain entries in Google Sheets. We can do so with VLOOKUP but with few additional tricks. In this tutorial, we will learn how to combine two or more criteria in VLOOKUP function in Google Sheets.
Since VLOOKUP scans the first column only of an array, we need to generate a helper column that will place the selected criteria to the first column. For this trick, we can merge the selected columns using the ARRAYFORMULA function.
For our example workbook, we have a small sheet named info that contains the name of the trainee, with the first and last name in separate columns, their expertise, and their branch assigned.
We would like our VLOOKUP to be able to parse first name and last name together in a single column. As a test, we can use the following formula (placing it in a separate sheet):
=ARRAYFORMULA({info!$A$1:info!$A$49&" "&info!$B$1:info!$B$49,info!$C$1:$C$49,info!$D$1:$D$49})
The result would look like this:
As you can see, the first name and last name were merged in the first column, separated by a space. To break it down further, here is how the formula above worked:
We are now ready for the next step.
Before we add VLOOKUP to our Formula, let us first make sure that we get the right settings for our VLOOKUP. For our example, we wish to find where a trainee is assigned to. Our formula will look like this:
=vlookup(F4,A1:C49,3,0)
Our result is as follows:
There is another way of doing this, which will not involve creating a fixed helper column but integrating it into the vlookup formula.
We will use the ARRAYFORMULA function, moving the range of cells enclosed in the curly braces into the range for the vlookup. So from
=ARRAYFORMULA({range_enclosed_in_curly_braces})
To
=ARRAYFORMULA(VLOOKUP(<cell_containing_the_search_string>,{range_enclosed_in_curly_braces},<column_to_be_displayed>,FALSE))</column_to_be_displayed></cell_containing_the_search_string>
For our example, we can write the actual formula as follows:
=arrayformula(vlookup(F13,{info!$A$1:info!$A$49&" "&info!$B$1:info!$B$49,info!$C$1:$C$49,info!$D$1:$D$49},3,0))
The result will look like this:
Let’s spice it up further by writing the formula such that we get both the branch assigned and their expertise, looking like:
Branch: <branch>, Expertise: <expertise></expertise></branch>
We will do so by formatting the new helper array (thus not modifying the original array) to include such details:
{info!$A$1:info!$A$49&" "&info!$B$1:info!$B$49,"Branch: "&info!$D$1:$D$49&", Expertise: "&info!$C$1:$C$49}
Then plugging it back to the formula:
=arrayformula(vlookup(F13,{info!$A$1:info!$A$49&" "&info!$B$1:info!$B$49,"Branch: "&info!$D$1:$D$49&", Expertise: "&info!$C$1:$C$49},2,0))
We will get this result:
To help you learn, here is the sample sheet we used to do this tutorial: VLOOKUP Multiple Criteria Sample Sheet
Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.
.png)
