How to Use VLOOKUP with Multiple Criteria?
Sometimes while working with data when we match the data to the reference Vlookup if finds the value first it displays the result and does not look for the next value, but what if the user wants the second result, this is another criteria, to use Vlookup with multiple criteria we need to use other functions with it such as choose function.
VLOOKUP Formula in Excel
Let us now see some examples of VLOOKUP function with multiple criteria search.
Suppose you have data of employees of your company. The data contains the name, their current salary, department, employee ID, as shown below.
You want to lookup at an employee by his/her name and department. Here, the search will include two details: Name and Department. The name and department to lookup for are given in cell G6 and G7.
To search for “Dhruv” from the “Sales” Department, first, make a separate column containing “Name”&” Department” of all the employees.
To do this for the first employee, use the Excel VLOOKUP Formula:
= C3 & D3
and press Enter. The cell will now contain “ManishIT.” It is important to add this column to the left of the data since the first column of the array range is considered for the lookup. Now, simply drag it to the rest of the cells.
To look up the value for “Dhruv” & “Sales” given in cell G6 and G7, you can use the Excel VLOOKUP Formula:
= VLOOKUP(H6 & H7, A3:E22, 5, FALSE)
It will return the salary of the looked up the employee, Dhruv, from the Sales Department.
Suppose you have the sales data for two different products for 12 months, as shown below.
You want to create a lookup table in excel, in which you enter the month and the product id, and it returns the sales for that product during that month.
To do this, you can use the VLOOKUP and Match Formula in excel:
= VLOOKUP( F4, A3:C14, MATCH( F5, A2:C2, 0 ), 0)
where the month you want to look up is given in F4, and the product name to lookup is given in F5.
In this case, it will return 13,000.
Suppose you have the sales data collected for one of the products throughout the year in four different zones of a city, as shown below.
Now you want to check if the month in which the sales were maximum for the East zone is also the month in which sales were maximum for the West zone. To check this, first, you need to make an additional column containing the sales for the east and west zone. In this case, we separate the values by <space>.
To add the additional column to the left, use the Excel VLOOKUP Formula:
= D3 & “ “ & E3
for the first cell of the table and press Enter. Then drag it to the rest of the cells.
Now calculate the maximum sales for East and West Zones separately. To calculate the maximum value, use the Excel VLOOKUP Formula:
= MAX(D3:D14) for East zone
(Learn more about Max Function in ExcelMax Function In ExcelThe MAX Formula in Excel is used to calculate the maximum value from a set of data/array. It counts numbers but ignores empty cells, text, the logical values TRUE and FALSE, and text values.)
and = MAX(E3:E14) for West Zone.
Now to check if the month for which sales were maximum for East Zone is also the month in which sales were maximum for West zone, you can use the:
= IFERROR( VLOOKUP( J4 & ” ” & J5, B3:C14, 2, 0), “NO”)
(Learn more about IFERROR Function in ExcelIFERROR Function In ExcelWhen an error occurs in any formula or argument, the IFERROR excel function is used to display the result. This function can be used in conjunction with other functions to alert the user of any errors in the formula.)
VLOOKUP( J4 & ” ” & J5, B3:C14, 2, 0) will search for the maximum value of East and West zone in the additional column. If it is able to find a match, it will return the corresponding month. Else, it will give an error.
IFERROR((VLOOKUP(..)), “NO”) : If the output from the VLOOKUP function is an error, it will return “NO” else. It will return the corresponding month.
Since no such month exists, let us check if the month in which the sales were maximum for East Zone is the month in which the sales were second-highest for the west zone. First, calculate the second-largest sales for the west zone by using.
= LARGE(E3:E14, 2)
(Learn more about LARGE Function Excel)
Now, use the syntax: =IFERROR(VLOOKUP(K4&” “&K5, B3:C14, 2, 0), “NO”)
It will return to Jun.
It is important to note here that there can be more than one month also in which the sales were maximum for the east and west zone, but the Excel VLOOKUP Formula will return one of those months only.
Thinks to Remember
- VLOOKUP function with multiple criteria is used to search value in a column and return the value from a corresponding column.
- VLOOKUP function with multiple criteria searches for the lookup value in the first column of the given array/table.
- If you want to search the VLOOKUP function with multiple criteria, such as value1 from the 1st column and value2 from the 2nd column, you need to add an additional column for the search. This additional column should be added to the left of the data such that it appears as the first column of the lookup table.
This has been a guide to VLOOKUP with multiple criteria. Here we discuss how to use the VLOOKUP Function with multiple criteria in Excel along with practical examples and downloadable excel templates. You may also look at these useful functions in excel –