Excel VLOOKUP Two Criteria
Those who have used VLOOKUP know how much time it has saved for them in their respective fields. All the beginners’ first formula would be in the list is always a VLOOKUP, in fact in almost all the job posting VLOOKUP is the essential formula required. Having said about the importance of VLOOKUP at the workplace one needs to understand after a certain extent basic VLOOKUP cannot help us to the full extent. Yes, we need to go to the next level to learn advanced VLOOKUP function. In this article, we will show you how to apply excel VLOOKUP for two criteria.
What does Two Criteria Mean?
VLOOKUP is used to fetch the data from one table to another table based on the common values available in both the tables i.e. Lookup Value.
For example, look at the below table in excel worksheet.
We have an employee table with quarterly revenue they have generated, on the right-hand side we have employee name & quarter drop-down list, 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 “Q3” result (Green Colored cells).
This is because VLOOKUP finds the first value for the “Peter” as “Q1” numbers so returned 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 column “Emp & Qtr” we need to combine employee name and a quarter together to get a unique value column.
We have used ampersand (&) symbol to concatenate employee name and a quarter, also between employee name and a quarter we have used underscore (_) character to differentiate employee name and quarter.
Now we need to apply the revised VLOOKUP function in two criteria, this time lookup value is “Emp & Qtr” instead of only employee name.
- Open the VLOOKUP function in the H2 cell.
- Now we need to use little brain while choosing the lookup value, first select employee name cell “F2”.
- Enter concatenate symbol ampersand (&).
- Now in the table, we have used underscore (_) character to separate employee name and a quarter so uses 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).
- Ok, that’s all close the bracket and hit enter key to get the result.
Here we go, we have got the accurate result this time, thanks to two criteria helper column and lookup value.
- We have created a drop-down list for employee name and quarter, based on the selection from the drop-down 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 drop-down list, so our formula showing the revised result accordingly.
Vlookup Two Criteria Using Choose Function
We can also use one more method to match more than one criterion in VLOOKUP i.e. using CHOOSE function.
- For this, we need not create any helper column, first open VLOOKUP function as select lookup values as shown above.
- For choosing Table Array open CHOOSE function now.
- Enter Index Number as 1, 2 in curly brackets.
- For Value1 choose the Employee column first.
- Next using ampersand combine underscore and quarter column.
- For Value 2 column choose “Revenue” column.
- We are done with CHOOSE function to select the Table Array, close the bracket of CHOOSE function and for Column Num & Range Lookup enter as usual values.
- Ok, we are done with the formula before we close out the formula one thing we need to remember is this is an array formula to we need to close the formula by holding three keys i.e. “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, so we can get an accurate result.
- Instead of using underscore as the combining character between two values you can use anything and the same needs to be used in lookup value as well.
This has been a guide to Vlookup Two Criteria. Here we discuss how to create vlookup two criteria in excel using CHOOSE function along with examples and downloadable excel template. You may also look at these useful functions in excel –