Advanced VLOOKUP in Excel: multiple, double, nested

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.

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)

For the detailed explanation of the formulas, please see:

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

Formula 1. Vlookup Nth instance

Suppose you have customer names in one column, the products they purchased in another, and you are looking to find the 2^nd or 3^rd product bought by a given customer.

The simplest way is to add a helper column to the left of the table like we did in the first example. But this time, we will populate it with customer names and occurrence numbers like "John Doe1", "John Doe2", etc.

To get the occurrence, use the COUNTIF function with a mixed range reference (the first reference is absolute and the second is relative like $B$2:B2). Since the relative reference changes based on a position of the cell where the formula is copied, in row 3 it will become $B$2:B3, in row 4 - $B$2:B4, and so on.

Concatenated with the customer name (B2), the formula takes this form:

=B2&COUNTIF($B$2:B2, B2)

The above formula goes to A2, and then you copy it down to as many cells as needed.

After that, input the target name and occurrence number in separate cells (F1 and F2), and use the below formula to Vlookup a specific occurrence:

=VLOOKUP(F1&F2, A2:C11, 3, FALSE)

Formula 2. Vlookup 2nd occurrence

If you are looking for the 2nd instance of the lookup value, then you can do without the helper column. Instead, create the table array dynamically by using the INDIRECT function together with MATCH:

=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)

Where:

• E1 is the lookup value
• A2:A11 is the lookup range
• B11 is the last (bottom-right) cell of the lookup table
  

Please note that the above formula is written for a specific case where data cells in the lookup table begin in row 2. If your table is somewhere in the middle of the sheet, use this universal formula, where A1 is the top-left cell of the lookup table containing a column header:

=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)

How this formula works

The core of the formula is the standard VLOOKUP function that searches for an exact match to the lookup value in I1. But since we do not know in which exactly column the sales for a specific month are, we cannot supply the column number directly to the col_index_num argument. To find that column, we use the following MATCH function:

MATCH(I2, A1:F1, 0)

Translated into English, the formula says: look up the I2 value in A1:F1 and return its relative position in the array. By supplying 0 to the 3rd argument, you instruct MATCH to find the value exactly equal to the lookup value (it's like using FALSE for the range_lookup argument of VLOOKUP).

Since Mar is in the 4th column in the lookup array, the MATCH function returns 4, which goes directly to the col_index_num argument of VLOOKUP:

VLOOKUP(I1, A2:F9, 4, FALSE)

Please pay attention that although the month names start in column B, we use A1:I1 for the lookup array. This is done in order for the number returned by MATCH to correspond to the column's position in table_array of VLOOKUP.

To learn more ways to perform matrix lookup in Excel, please see INDEX MATCH MATCH and other formulas for 2-dimensional lookup.

Formula 1. INDIRECT VLOOKUP to dynamically pull data from different sheets

If your task is to retrieve data from multiple sheets, a VLOOKUP INDIRECT formula is the best solution – compact and easy-to-understand.

For this example, we organize the summary table in this way:

• Input the products of interest in A2 and A3. Those are our lookup values.
• Enter the unique parts of the named ranges in B1, C1 and D1.

And now, we concatenate the cell containing the unique part (B1) with the common part ("_Sales"), and feed the resulting string to INDIRECT:

INDIRECT(B$1&"_Sales")

The INDIRECT function transforms the string into a name that Excel can understand, and you put it in the table_array argument of VLOOKUP:

=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)

The above formula goes to B2, and then you copy it down and to the right.

Please pay attention that, in the lookup value ($A2), we've locked the column coordinate with absolute cell reference so that the column remains fixed when the formula is copied to the right. In the B$1 reference, we locked the row because we want the column coordinate to change and supply an appropriate name part to INDIRECT depending on the column into which the formula is copied:

If your main table is organized differently, the lookup values in a row and unique parts of the range names in a column, then you should lock the row coordinate in the lookup value (B$1) and the column coordinate in the name parts ($A2):

=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)

Formula 2. VLOOKUP and nested IFs to look up multiple sheets

In situation when you have just two or three lookup sheets, you can use a fairly simple VLOOKUP formula with nested IF functions to select the correct sheet based on the key value in a particular cell:

=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)

Where $A2 is the lookup value (item name) and B$1 is the key value (state):

In this case, you do not necessarily need to define names and can use external references to refer to another sheet or workbook.

For more formula examples, please see How to VLOOKUP across multiple sheets in Excel.