Excel VLOOKUP Two Criteria

Article byJeevan A Y
Edited byNannila Jai Ratna
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Excel VLOOKUP Two Criteria?

VLOOKUP Two Criteria in excel is used to look for value based on 2 criteria, classified commonly using ampersand.

VLOOKUP, Vertical LOOKUP is a function used to look for values in a vertical column. In fact, VLOOKUP is the most used LOOKUP function in excel.

Vlookup Formula

Though VLOOKUP is useful, basic VLOOKUP function cannot help us to the full extent. need to learn advanced VLOOKUP function. This article will show you how to apply Excel VLOOKUP for two criteria.

Key Takeaways

  • VLOOKUP (Vertical LOOKUP) function is used to look for values in vertical column. VLOOKUP with Two criteria is used to search for value in a column and return the value from a corresponding column.
  • The VLOOKUP function with multiple criteria searches for the lookup_value in the first column of the given array/table.
  • Remember, to search the VLOOKUP function with two criteria, for example, value1 from first column and value2 from second column, we need to add a column for the search.

What Do Two Criteria Mean?

VLOOKUP is used to fetch the data from one table to another based on the common values available in both the tables, Lookup Value.

You can download this Vlookup Two Criteria Excel Template here – Vlookup Two Criteria Excel Template

For example, look at the below table in the Excel worksheet.

Vlookup Two Criteria - Example 1

We have an employee table with the quarterly revenue they have generated. On the right-hand side, we have an employee name and quarter drop-down list in excel, so when we choose the employee name and quarter. We should get the revenue details of the selected employee for the selected quarter.

The problem with VLOOKUP here is when we apply the VLOOKUP two criteria based on employee name. We get the first value in the table for the selected employee. For example, look at the below formula applied.

Vlookup Two Criteria - Example 1-1

We have applied VLOOKUP for the employee “Peter” for the quarter “Q3”, but look at the result VLOOKUP has given us the “Q1” result (yellow-colored cells) instead of the “Q3” result (green-colored cells).

It is because VLOOKUP finds the first value for the “Peter” as “Q1” numbers, so it returns the same. VLOOKUP always gives the result from the first available value. So, this is where the concept of Two Criteria is important. We need to create a new column to have a unique value list.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

VLOOKUP Excel Function Video Explanation


How To Create Unique Lookup Value Column?

We have duplicate names, but for each duplicate name, we have different quarter numbers. So, using these two columns, we can create a unique column called “Emp & Qtr.”

  • For the column “Emp & Qtr,” we need to combine the employee name and a quarter to get a unique value column.
Unique Lookup Value - Example 1

We have used the ampersand (&) symbol to concatenate employee names and a quarter. In addition, used underscore (_) character to differentiate employee name and quarter between employee name and quarter.

Now, we need to apply the revised VLOOKUP function to two criteria. This time lookup_value is “Emp & Qtr” instead of only the employee name.

  • Open the VLOOKUP function in the H2 cell.
Unique Lookup Value - Example 1-1
  • Now, while choosing the lookup_value. First select the employee name cell “F2”.
Unique Lookup Value - Example 1-2
Unique Lookup Value - Example 1-3
  • In the table, we have used the underscore (_) character to separate the employee name and a quarter. So, use the same here before we select the quarter cell G2.
Unique Lookup Value - Example 1-4
  • Now again, put the ampersand symbol and choose quarter cell G2.
Unique Lookup Value - Example 1-5
  • Now, this lookup value is similar to the one we have created in the table, now choose the table array from column A to column D.
Unique Lookup Value - Example 1-6
  • Mention the column number from the table as 4 and range_lookup as 0 (FALSE).
Vlookup Two Criteria - Example 1-7
  • Close the bracket and press the “Enter” key to get the result.
Vlookup Two Criteria - Example 1-8

This time, we got the accurate result thanks to two criteria: helper column and lookup_value.

  • We have created a dropdown list for employee names and quarters. Based on the selection from the dropdown list, it will show the revenue details for the selected employee and quarter.
Vlookup Two Criteria - Example 1-9

Look at the above picture. We have just changed the employee name and quarter from the dropdown list. So our formula shows the revised result accordingly.

Vlookup Two Criteria Using Choose Function

We can also use one method to match more than one criterion in VLOOKUP, using the CHOOSE function in excel.

  • For this, we need not create any helper column. First, open the VLOOKUP function and select lookup values as shown above.
Using CHOOSE Function 1
  • For choosing Table Array, open the CHOOSE function now.
Using CHOOSE Function 1-1
  • Enter Index Number as 1, 2 in curly brackets.
Using CHOOSE Function 1-2
  • For Value1, choose the “Employee Name” column first.
Using CHOOSE Function 1-3
  • Next, using an ampersand, combine the underscore and “Quarter” column.
Using CHOOSE Function 1-4
  • For the Value 2 column, choose the “Revenue” column.
Using CHOOSE Function 1-5
  • We are done with the CHOOSE function to select the Table Array, close the bracket of the CHOOSE function. For column_num and range_lookup,enter as usual values.
Using CHOOSE Function 1-6
  • We are done with the formula. Before we close out the formula, we must remember that this is an array too. We need to close the formula by holding three keys: “Ctrl + Shift + Enter.”
Using CHOOSE Function 1-7

Important Things To Note

  • VLOOKUP can fetch the first value found in the table for duplicate lookup values.
  • Combining values will give us a unique lookup value to get an accurate result.
  • Instead of using an underscore as the combining character between two values, we can use anything. The same needs to be used in the lookup_value as well.

Frequently Asked Questions

1. What is VLOOKUP and VLOOKUP Two criteria?

VLOOKUP is one of the LOOKUP functions in excel. It is used to look for a particular value from the vertically arranged table.

But, VLOOKUP with two criteria is an advanced VLOOKUP function where we can look for a value in a column and return the value from a corresponding column.

2. When to use VLOOKUP two criteria?

To find a value using 2 criteria effectively in large datasets, we can use VLOOKUP with two criteria. Especially, when we have to fetch a value by searching from one column and returning value from another, we can use VLOOKUP two criteria.

3. How to use VLOOKUP Two criteria?

We can use VLOOKUP To criteria in simple steps. Consider the below table where name of the students, semester and the marks obtained in each semester is shown in columns A, B, C and D, respectively.

VLookup Two criteria FAQ 3

The steps to use VLOOKUP two criteria are as follows:

Step 1: Let us assume that we have to look for Cindy’s marks obtained in Semester 2.
Step 2: Next, enter the VLOOKUP formula with concatenate symbol ampersand (&) underscore (_) character.

So, the entered formula is =VLOOKUP(F2&”_”&G2,A2:D16,4,0)

FAQ 3 - Step 2

Step 3: Then, press the Enter key to get the result.
We can see the result in cell H2.

VLookup Two criteria FAQ 3 - Step 3

Likewise, we can use VLOOKUP Two criteria.

This article has been a guide to VLOOKUP Two Criteria. Here, we discuss creating VLOOKUP two criteria in excel using the CHOOSE function, examples, and a downloadable Excel template. You may also look at these useful functions in Excel: –