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.
Creating a helper column
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:
- The curly braces merged the ranges into a single range. By itself, this is not necessary, but we prepared this to allow for the use of VLOOKUP later on.
- info!$A$1:info!$A$49&" "&info!$B$1:info!$B$49 - merged the column for first and last name, with a space as separator. & is used to merge cells. You can read more here.
- The comma separated the other ranges that went to separate columns: info!$C$1:$C$49 and info!$D$1:$D$49.
We are now ready for the next step.
Adding VLOOKUP to our Formula
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:
A sample sheet to help you learn!
To help you learn, here is the sample sheet we used to do this tutorial: VLOOKUP Multiple Criteria Sample Sheet
Recommended articles
More resources
