VLOOKUP with Multiple Criteria (Table of Contents)

• VLOOKUP with Multiple Criteria
• How to use VLOOKUP with Multiple Criteria in Excel?

VLOOKUP with Multiple Criteria

It is easy to search for a single value in Vlookup as you have seen in the above illustration. However, if you want to look up for some “ID” and “Symbol”, the function will not be able to handle both the searches. It is because the VLOOKUP formula searches the lookup_value against the first column. If one value is in the first column and the second value is in the second column, it will not able to find the match. When the values in the first column are redundant, it is important to include the additional information for the search. In such cases, you can include an additional column concatenating the first and second (or any other column) field. This additional column can then be used for the specific search you want to carry out.

VLOOKUP Formula in Excel

The VLOOKUP formula in its simplest form can be used as:

The VLOOKUP formula in Excel is one of the lookup and reference functions in which you can search things in a table or a range by row. For example, you can look up for the price of a product, marks of a student, etc.

= VLOOKUP( Value to look up, range in which the value to lookup in, the column number in the given range containing the return value, whether you want an Exact Match or an Approximate Match – given as 0/FALSE or 1/TRUE).

Arguments:

Argument 1		Lookup Value		The value to look up in the range. The value can be numeric, character or string.
Argument 2		Range		The range of cells in which the lookup value and the return value is located. The lookup value should be in the first column of this range.
Argument 3		Column number		The column number in the given range of the required return value. Suppose the range is A3:C15 and the required value is in column C, then you the column number will be 3.
Argument 4		Type of match		Optional argument. If you want an approximate match, you can indicate TRUE/1 else indicate FALSE/0 for an exact match. By default, it searches for an approximate match

The above arguments should be in the exact same order. A change in the order of arguments will give an error or false results.

How to Use VLOOKUP with Multiple Criteria?

Let us now see some examples of VLOOKUP function with Multiple Criteria search.

VLOOKUP with Multiple Criteria – Example #1

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 of 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 is given in cell G6 and G7.

To search for “Dhruv” from “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 Sales Department.

VLOOKUP with Multiple Criteria – 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 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 Excel VLOOKUP and Match Formula:

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

VLOOKUP with Multiple Criteria – Example #3

Suppose you have the sales data collected for one of the product 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 East zone is also the month in which sales were maximum for West zone. To check this, first, you need to make an additional column containing the sales for 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 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 Excel)

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

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

It is important to note here that there can be more than one month also in which the sales were maximum for east and west zone but the Excel VLOOKUP Formula will return one of those months only.

Thinks to Remember about VLOOKUP with Multiple Criteria:

• 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 VLOOKUP function with multiple criteria, such as value1 from the 1^st column and value2 from the 2^nd 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.

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

Recommended Articles

This has been a guide to VLOOKUP with multiple criteria. Here we discuss how to use 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 –

• VLOOKUP vs HLOOKUP
• IFERROR with VLOOKUP in Excel
• COUNTIF with Multiple Criteria
• Top 20 Shortcuts in Excel

The post VLOOKUP with Multiple Criteria appeared first on Learn Investment Banking: Financial Modeling Training Courses Online.