VLOOKUP with Multiple Criteria

Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

How to Use VLOOKUP with Multiple Criteria?

Sometimes while working with data, when we match the data to the reference VLOOKUP, if it 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? It is another criterion. To use VLOOKUP with multiple criteria, we need to use other functions, such as the CHOOSE function.

VLOOKUP Formula in Excel

VLookup Function Formula

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

You can download this VLOOKUP with Multiple Criteria template here – VLOOKUP with Multiple Criteria template

Example #1

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

VLOOKUP with multiple criteria Example 1

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.

VLOOKUP with multiple criteria Example 1-1

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

VLOOKUP with multiple criteria Example 1-2

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.

VLOOKUP with multiple criteria Example 1-3

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)

VLOOKUP with multiple criteria Example 1-4

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

VLOOKUP with multiple criteria Example 1-5

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

Example #2

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

VLOOKUP with multiple criteria Example 2

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.

VLOOKUP with multiple criteria Example 2-2

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.

VLOOKUP with multiple criteria Example 2-3

In this case, it will return 13,000.

VLOOKUP with multiple criteria Example 2-4

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.

Example 3

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

Example 3-1

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

Example 3-2

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)

Example 3-3

= MAX(E3:E14) for West Zone.

Example 3-4

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)

Example 3-5

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)

Example 3-6

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

Example 3-7

It will return “Jun.

Example 3-8

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

Reader Interactions

Comments

  1. Archana says

    Super.. U made clearly understand to everyone. I like to learn excel. It’s interesting

Leave a Reply

Your email address will not be published. Required fields are marked *