How to use VLOOKUP with Multiple Criteria in Google Sheets
Learn how to combine two or more criteria in the VLOOKUP function on Google Sheets. Great for data analysis with multiple variables.
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):
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:
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