VLOOKUP is utilized to locate a value in the initial column of a table array and retrieve a value from the same row in a different column. However, what if we need to search for a value using multiple criteria? VLOOKUP can handle multiple criteria with a few adjustments!

First, let's briefly review the VLOOKUP syntax:

Formula breakdown:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

What it means:
=VLOOKUP(this value, in this list, and get me the value in this column, Exact Match/FALSE/0])

The VLOOKUP syntax does not support searching for multiple criteria directly. Nevertheless, with some modifications, we can use VLOOKUP with multiple criteria to easily find data and enhance the data analysis process in Excel. This article will cover three straightforward methods for using VLOOKUP with multiple criteria in Excel.

Let's delve into each of these methods in detail!

Download the Excel Workbook below to follow along and learn how to use VLOOKUP with Multiple Criteria in Excel –

download excel workbookVLOOKUP-with-Multiple-Criteria.xlsx

Method 1: Using a Helper Column

We can establish a new column that merges the two columns containing the criteria. Ensure that this column is positioned at the start of the table since Excel searches the left-most column of the table array.

Suppose we have a dataset with details about sales representatives, regions, and sales amounts. We aim to use VLOOKUP to match two criteria:

• The Sales Representative should be Tom
• The Region should be South

Follow these steps to use VLOOKUP with multiple criteria using a helper column:

STEP 1: Insert a Helper Column before the Sales Rep Column.

STEP 2: Use the following formula to combine the two lookup columns, i.e., Sales Rep & Region:

=B2&C2

STEP 3: Select the range A2: A25 and press Ctrl D to apply the formula to the entire column.

STEP 4: Enter the VLOOKUP formula.

=VLOOKUP(

STEP 5: Enter the first argument, i.e., lookup_value. Here, it is the combination of cells G2 and G3, i.e., G2&G3.

=VLOOKUP(G1&G2,

STEP 6: Enter the second argument, i.e., table_array. Here, it is the range A:D.

=VLOOKUP(G1&G2,A:D,

STEP 7: Enter the third argument, i.e., col_index_num. Here, it is 4 because the result is in the 4th column of the table array.

=VLOOKUP(G1&G2,A:D,4,

STEP 8: Enter the fourth argument, i.e., range_lookup. Here, it is 0 for an exact match.

=VLOOKUP(G1&G2,A:D,4,0)

As you can see, VLOOKUP has successfully retrieved the sales amount for the sales representative named Tom in the southern region.

Method 2: Combining VLOOKUP and MATCH

The MATCH function in Excel identifies the position of an item within a list or a range of cells. It can be employed to determine the position of a specific item within a range.

Formula breakdown:

=MATCH(lookup_value, lookup_array, [match_type])

What it means:

=MATCH(lookup this value, from this list or range of cells, return me the Exact Match)

Let's use a combination of VLOOKUP & MATCH to find the sales amount for a sales representative named Bruce in the Eastern region:

STEP 1: Enter the VLOOKUP function.

=VLOOKUP(

STEP 2: Enter the first argument, i.e., lookup_value. Here, it is the region we need to find, mentioned in cell H2.

=VLOOKUP(H2,

STEP 3: Enter the second argument, i.e., table_array. Here, it is A:E.

=VLOOKUP(H2,A:E,

STEP 4: Now, we will use the MATCH function to obtain the column number.

=VLOOKUP(H2,A:E,MATCH(

STEP 5: Enter the first argument of the MATCH function, i.e., lookup_value. Here, it is the sales representative mentioned in cell H1.

=VLOOKUP(H2,A:E,MATCH(H1,

STEP 6: Enter the second argument of the MATCH function, i.e., lookup_array. Here, it is A1:E1.

=VLOOKUP(H2,A:E,MATCH(H1,A1:E1,

STEP 7: Enter the third argument of the MATCH function, i.e., match_type. Here, it is 0 for an exact match.

=VLOOKUP(H2,A:E,MATCH(H1,A1:E1,0)

STEP 8: Enter the fourth argument of the VLOOKUP function, i.e., range_lookup. Here, it is 0 for an exact match.

=VLOOKUP(H2,A:E,MATCH(H1,A1:E1,0),0)

The sales amount for Bruce in the East region has been extracted using the combination of VLOOKUP & MATCH.

Method 3: Using Array Function

We can utilize the CHOOSE function to specify columns and their order for the table array used in the VLOOKUP function.

Formula breakdown:

=CHOOSE({index_num}, value1, [value2], …)

Here, we are employing the array function to define the index numbers, i.e., the number of columns the table should contain, and value1, value2, etc., to define the array columns. By using the COLUMN function, we can apply VLOOKUP with multiple columns.

Let's explore this with an example:

STEP 1: Enter the VLOOKUP formula.

=VLOOKUP(

STEP 2: Enter the first argument, i.e., lookup_value. Here, it is the sales representative mentioned in cell F1 and the region mentioned in cell F2.

=VLOOKUP(F1&F2,

STEP 3: We will now insert the CHOOSE function to get the table array. The first argument defines the number of columns we need to add.

=VLOOKUP(F1&F2,CHOOSE({1,2},

STEP 4: Enter the value arguments for the CHOOSE function, i.e., the column ranges. First, we will insert the lookup columns combined using the & sign, then add the result column separated by a comma.

=VLOOKUP(F1&F2,CHOOSE({1,2},A2:A27&B2:B27,C2:C27)

STEP 5: Enter the third argument of the VLOOKUP function, i.e., col_index_num. Here, it is the 2nd column, so the value will be 2.

=VLOOKUP(F1&F2,CHOOSE({1,2},A2:A27&B2:B27,C2:C27),2

STEP 6: Enter the fourth argument of the VLOOKUP function, i.e., range_lookup. Here, it is 0 for an exact match.

=VLOOKUP(F1&F2,CHOOSE({1,2},A2:A27&B2:B27,C2:C27),2,0)

As you can see, VLOOKUP has successfully retrieved the sales amount for the sales representative named Tom in the southern region.

Please Note – For large datasets, using VLOOKUP with multiple criteria might not be the most efficient approach. It would be more effective to use alternatives such as the INDEX MATCH function, Pivot Tables, Power Query, etc.

Click here to learn more about lookup functions in Excel!

The above is the detailed content of Mastering VLOOKUP with Multiple Criteria in Excel – 3 Quick and Easy Methods. For more information, please follow other related articles on the PHP Chinese website!