VLOOKUP with Multiple Criteria

Updated on April 22, 2024
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.

data of employees

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.

name and department to look up

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

= C3 & D3

first employee, use the Excel VLOOKUP Formula

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.

Press the Enter key. The cell will now contain ManishIT

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)

use the Excel VLOOKUP formula

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

salary of the lookup employee

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

sales data for two different products

You want to create a lookup table in excel, 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.

create a lookup table in excel

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.

product name to lookup is in F5

In this case, it will return 13,000.

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.

sales data collected for one of the products

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

add the additional column to the left

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

table's first cell, press the Enter key

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 Excel)

Calculate the maximum sales

= MAX(E3:E14) for West Zone.

for West Zone result

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 Excel)

sales were maximum

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 Excel)

calculate the second-largest sales

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

Now, use the syntax IFERROR

It will return “Jun.

It will return Jun result

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 *