Excel VLOOKUP Two Criteria

Those who have used VLOOKUP know how much time it has saved them in their respective fields. All the beginners’ first formula would be in the list is always a VLOOKUP. In almost all job postings, VLOOKUP is the essential formula required. Having said this, the importance of VLOOKUP at the workplace, one needs to understand, to a certain extent. The basic VLOOKUP cannot help us to the full extent. We must go to the next level to learn the advanced VLOOKUP function. This article will show you how to apply Excel VLOOKUP for two criteria.

What do Two Criteria Mean?

VLOOKUPVLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more is used to fetch the data from one table to another based on the common values available in both the tables, Lookup Value.

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

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 excelDrop-down List In ExcelA drop-down list in excel is a pre-defined list of inputs that allows users to select an option.read more, 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.

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.

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.

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.

  • Now, we need to use little brains while choosing the lookup_value. First select the employee name cell “F2”.

  • Enter concatenateConcatenateThe CONCATENATE function in Excel helps the user concatenate or join two or more cell values which may be in the form of characters, strings or numbers.read more symbol ampersand (&).

  • 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.

  • Now again, put the ampersand symbol and choose quarter cell G2.

  • 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.

  • Mention the column number from the table as 4 and range_lookup as 0 (FALSE).

  • Close the bracket and press the “Enter” key to get the result.

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.

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 excelCHOOSE Function In ExcelChoose Function returns a value from the list of values in a given range. This function takes two mandatory arguments: the index number and the first value. The other values are optional to mention.read more.

  • For this, we need not create any helper column. First, open the VLOOKUP function and select lookup values as shown above.

  • For choosing Table Array, open the CHOOSE function now.

  • Enter Index Number as 1, 2 in curly brackets.

  • For Value1, choose the “Employee Name” column first.

  • Next, using an ampersand, combine the underscore and “Quarter” column.

  • For the Value 2 column, choose the “Revenue” column.

  • 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.

  • 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.”

Things to Remember

  • 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.

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: –

  • Multiple Criteria of VLookupVLookup to Return Multiple ValuesVLOOKUP ErrorsCompare – VLOOKUP vs. HLOOKUP