How to Use VLOOKUP with Multiple Criteria?

VLOOKUP Formula in Excel

Let us now see examples of the VLOOKUP function with multiple criteria search.

Example #1

Suppose you have data of employees of your company. The data contains the “Name,” “Current Salary,” “Department,” and “ID,” as shown below.

You want to look up an employee by their name and department. The search will include two details: “Name” and “Department.” The name and department to look up for are given in cells G6 and G7.

To search for “Dhruv” from the “Sales” Department, first, make a separate column containing the “Name” and “Department” of all the employees.

To do this for the first employee, use the Excel VLOOKUP FormulaVLOOKUP FormulaThe 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:

= C3 & D3

Press the “Enter” key. 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, drag it to the rest of the cells.

To look up the value for “Dhruv” and “Sales” given in cells G6 and G7, you can use the Excel VLOOKUP formula:

= VLOOKUP(H6 & H7, A3:E22, 5, FALSE)

It will return the salary of the lookup employee, Dhruv, from the sales department.

Example #2

Suppose you have the sales data for two different products for 12 months, as shown below.

You want to create a lookup table in excelLookup Table In ExcelLookup tables are simply named tables that are used in combination with the VLOOKUP function to find any data in a large data set. We can select the table and name it, and then type the table’s name instead of the reference to look up the value.read more, in which you enter the “Month” and the “Product ID,” (product 1 and product 2 in this case),which returns the sales for that product during that month.

To do this, you can use the VLOOKUP and Match Formula in excelMatch Formula In ExcelThe MATCH function looks for a specific value and returns its relative position in a given range of cells. The output is the first position found for the given value. Being a lookup and reference function, it works for both an exact and approximate match. For example, if the range A11:A15 consists of the numbers 2, 9, 8, 14, 32, the formula “MATCH(8,A11:A15,0)” returns 3. This is because the number 8 is at the third position. read more:

= VLOOKUP( F4, A3:C14, MATCH( F5, A2:C2, 0 ), 0)

The month you want to look up is given in F4, and the product name to lookup is in F5.

In this case, it will return 13,000.

Example #3

Suppose you have the sales data collected for one of the products throughout the year in four different city zones, 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 .

To add the additional column to the left, use the Excel VLOOKUP formula:

= D3 & “ “ & E3

For the table’s first cell, press the “Enter” key. Then, drag it to the rest of the cells.

Calculate the maximum sales for “East Zone” and “West Zone” separately. To calculate the maximum value, use the Excel VLOOKUP formula:

= MAX(D3:D14) for East zone

(Learn more about the 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.read more)

= MAX(E3:E14) for West Zone.

Now, to check if the month for which sales were maximum for the “East Zone” is also the month in which sales were maximum for the “West Zone,” you can use the following:

= IFERROR( VLOOKUP( J4 & ” ” & J5, B3:C14, 2, 0), “NO”)

(Learn more about the IFERROR Function in ExcelIFERROR Function In ExcelThe IFERROR function in Excel checks a formula (or a cell) for errors and returns a specified value in place of the error.read more)

VLOOKUP( J4 &”” & J5, B3:C14, 2, 0) will search for the maximum “East Zone” and “West Zone” values in the additional column. If it can 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 the “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 the LARGE Function ExcelLARGE Function ExcelLARGE Function returns the nth largest value from a given set of values. It is a built-in function of Microsoft Excel and is categorized as a Statistical Excel Function. read more)

Now, use the syntax: =IFERROR(VLOOKUP(K4&” “&K5, B3:C14, 2, 0), “NO”)

It will return “Jun.”

It is important to note that there can be more than one month in which the sales were maximum for the “East Zone” and “West Zone,” but the Excel VLOOKUP formula will only return one of those months.

Things to Remember

  • The VLOOKUP function with multiple 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.If you want to search the VLOOKUP function with multiple criteria, such as value1 from the first column and value2 from the second column, you need to add a column for the search. Therefore, we should add this additional column to the left of the data to appear as the first column of the lookup table.

This article is a guide to VLOOKUP with multiple criteria. Here, we discuss using the VLOOKUP function with multiple criteria in Excel, practical examples, and downloadable Excel templates. You may also look at these useful functions in Excel: –

  • VLookup Function with IFVLookup Function With IFIn Excel, vlookup is a reference function, and IF is a conditional statement. Based on the results of the Vlookup function, they locate a value that meets the criteria and also matches the reference value.read moreVLookup Function in VBAVLookup Function In VBAThe functionality of VLOOKUP in VBA is similar to that of VLOOKUP in a worksheet, and the method of using VLOOKUP in VBA is through an application. Method WorksheetFunctionread moreVlooKup AlternativesVlooKup AlternativesTo reference data in columns from right to left, we can combine the index and match functions, which is one of the best Excel alternatives to Vlookup.read moreVLOOKUP ErrorVLOOKUP ErrorThe top four VLOOKUP errors are - #N/A Error, #NAME? Error, #REF! Error, #VALUE! Error.read more