Advanced VLOOKUP In Excel: Multiple, Double, Nested - Ablebits

How to Vlookup multiple criteria

The Excel VLOOKUP function is really helpful when it comes to searching across a database for a certain value. However, it lacks an important feature - its syntax allows for just one lookup value. But what if you want to look up with several conditions? There are a few different solutions for you to choose from.

Formula 1. VLOOKUP with two criteria

Suppose you have a list of orders and want to find the quantity based on 2 criteria, Customer name and Product. A complicating factor is that each customer ordered multiple products, as shown in the table below:

A usual VLOOKUP formula won't work in this situation because it returns the first found match based on a single lookup value that you specify.

To overcome this, you can add a helper column and concatenate the values from two lookup columns (Customer and Product) there. It is important that the helper column should be the leftmost column in the table array because it's where Excel VLOOKUP always searches for the lookup value.

So, add a column to the left of your table and copy the below formula across that column. This will populate the helper column with the values from columns B and C (the space character is concatenated in between for better readability):

=B2&" "&C2

And then, use a standard VLOOKUP formula and place both criteria in the lookup_value argument, separated with a space:

=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)

Or, input the criteria in separate cells (G1 and G2 in our case) and concatenate those cells:

=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)

As we want to return a value from column D, which is fourth in the table array, we use 4 for col_index_num. The range_lookup argument is set to FALSE to Vlookup an exact match. The screenshot below shows the result:

In case your lookup table is in another sheet, include the sheet's name in your VLOOKUP formula. For example:

=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)

Alternatively, create a named range for the lookup table (say, Orders) to make the formula easier-to-read:

=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)

For more information, please see How to Vlookup from another sheet in Excel.

Note. For the formula to work correctly, the values in the helper column should be concatenated exactly the same way as in the lookup_value argument. For example, we used a space character to separate the criteria in both the helper column (B2&" "&C2) and VLOOKUP formula (G1&" "&G2).

Formula 2. Excel VLOOKUP with multiple conditions

In theory, you can use the above approach to Vlookup more than two criteria. However, there are a couple of caveats. Firstly, a lookup value is limited to 255 characters, and secondly, the worksheet's design may not allow adding a helper column.

Luckily, Microsoft Excel often provides more than one way to do the same thing. To Vlookup multiple criteria, you can use either an INDEX MATCH combination or the XLOOKUP function recently introduced in Office 365.

For example, to look up based on 3 different values (Date, Customer name and Product), use one of the following formulas:

=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))

=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)

Where:

• G1 is criteria 1 (date)
• G2 is criteria 2 (customer name)
• G3 is criteria 3 (product)
• A2:A11 is lookup range 1 (dates)
• B2:B11 is lookup range 2 (customer names)
• C2:C11 is lookup range 3 (products)
• D2:D11 is the return range (quantity)

Note. In all versions except Excel 365, INDEX MATCH should be entered as an CSE array formula by pressing Ctrl + Shift + Enter. In Excel 365 that supports dynamic arrays it also works as a regular formula.

For the detailed explanation of the formulas, please see:

• XLOOKUP with multiple criteria
• INDEX MATCH formula with multiple criteria