How to Use VLOOKUP with Multiple Criteria in Google Sheets

Using VLOOKUP with multiple criteria in Google Sheets requires a combination of functions like ArrayFormula, IF, and VLOOKUP. In this guide, we will be using an example to illustrate how to use VLOOKUP with multiple criteria in Google Sheets.

Example

Dataset

Assume we have a dataset of employees with the following columns: Employee ID, First Name, Last Name, Department, and Salary.

Problem

We want to find the salary of an employee using their First Name, Last Name, and Department as criteria.

Solution

1. First, create a helper column that combines the First Name, Last Name, and Department columns. In this example, we'll add the helper column in Column F.In cell F2, enter the following formula and then drag it down for all rows:

   =B2&"_"&C2&"_"&D2

   The dataset should now look like this:

   Employee ID	First Name	Last Name	Department	Salary	Helper Column
   1	John	Doe	HR	45000	John_Doe_HR
   2	Jane	Smith	Finance	50000	Jane_Smith_Finance
   3	Mike	Johnson	IT	55000	Mike_Johnson_IT
   4	Emily	Brown	IT	48000	Emily_Brown_IT
   5	Katie	Turner	HR	40000	Katie_Turner_HR

2. Next, use the combined VLOOKUP formula to find the salary based on the multiple criteria. In an empty cell, enter the following formula:

   =ArrayFormula(VLOOKUP(H2&"_"&I2&"_"&J2, F2:G6, 2, FALSE))

   In this formula, H2, I2, and J2 are cells where you will enter the First Name, Last Name, and Department criteria, respectively. F2:G6 is the range of the helper column and the salary column.
3. Now, you can enter the criteria in cells H2, I2, and J2, and the formula will return the salary of the employee that matches the criteria.For example, if you enter "John" in H2, "Doe" in I2, and "HR" in J2, the formula will return 45000 as the salary.

That's it! You have successfully used VLOOKUP with multiple criteria in Google Sheets.