Using VLOOKUP with multiple criteria from drop-down lists

Excel

VLOOKUP cannot accurately retrieve data from tables where the leftmost column does not have unique text values. But, with this trick you can use two (or more) Data Validation lists to select unique data from a table.

This VLOOKUP trick works well in situations where you need to specify criteria from two columns in the table to retrieve data, for example, you must specify the salesperson and the region to get a unique match. For this trick to work you will create a new leftmost column. That new column will contain a unique value you calculate by concatenating (joining) together the two columns you will search on, for example, salesperson&region.

The following figure shows a table of revenue by product, region, and month in the range $D$26:$Q$41. You can’t retrieve revenue from this table for a specific product, region, and month using a normal VLOOKUP function because there are multiple rows with the same region and with the same product.

Download the Excel samples files for VLOOKUP

Use VLOOKUP with multiple criteria and selecting data from two-drop down lists.

Use VLOOKUP with multiple criteria and selecting data from two-drop down lists.

To make a column of unique values you must create the calculated column shown in C25:241 that has the heading Index. This column joins together values from the Product column and the Region column to create a unique value. This Index column contains a unique index key. The index key in $C$26:$C$41 is a concatenation of the product and region. The formula to do this in C26 is

=D26&E26

That formula is copied down through C26:C41. VLOOKUP can now search on the unique values in this column.

The next thing that must be done is to specify what the user wants to retrieve. To do this, cells C6 and D6 use the Data Validation command to create Data Validation drop-down lists of allowed selections from the product list in C9:C12 and the region list in D9:D12. Clicking in cells C6 or D6 drops down a list for the user to select from.

When the user selects Product and Region from the Data Validation lists in C6 and D6 a formula in C21 joins the two selections together to make a unique criteria for VLOOKUP. The formula that does this in C21 is

=$C$6&$D$6

In the example shown below, the concatenation sign, &, joins Star and South to produce StarSouth.

Now, VLOOKUP can match the unique StarSouth in C21 against the calculated Index values in C26:C41.

The VLOOKUP searches on two concatenated values that create a unique index.

The VLOOKUP searches on two concatenated values that create a unique index.

The formula in F21 to retrieve data from the table for the month Jan is,

=VLOOKUP($C$21,$C$26:$Q$41,F$23,FALSE)

where

$C$21 is the concatenate choices from the Data Validation lists

$C26:$Q$41 is the range of the data table with the new calculated Index column as the leftmost column

F$23 is the number 4 indicating the fourth column in the table

FALSE  to indicate an exact match

Notice that when this formula is copied across row 21 the column from which data is retrieved changes to match the month specified in row 23.

Download the Excel samples files for VLOOKUP

Share the power...