These examples will guide you through using Vlookup with multiple criteria, retrieving a specific or all matching instances, performing dynamic Vlookups across multiple sheets, and more.

This is the second installment of our series designed to help you master Excel’s VLOOKUP function. These examples assume you're already familiar with how VLOOKUP works. If not, we recommend starting with the fundamental uses of VLOOKUP in Excel.

Before we proceed, let’s quickly revisit the function syntax:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Now that we're aligned, let's explore some advanced VLOOKUP formulas:

Excel’s VLOOKUP function is extremely useful when searching through a dataset for a specific value. However, it has a limitation — it only supports a single lookup value. What if you need to search using multiple conditions? Fortunately, several effective solutions are available.

Formula 1. VLOOKUP with two criteria

Imagine you have an order list and want to find the quantity based on two conditions: Customer Name and Product. The challenge is that each customer has ordered multiple products, as shown below:

A standard VLOOKUP won’t suffice here, as it only returns the first match for a single lookup value.

To solve this, insert a helper column and concatenate the values from the two criteria columns (Customer and Product) into it. This helper column must be the leftmost column in your table array since VLOOKUP always searches in the first column.

Insert a new column to the left of your data and use this formula to combine the values from columns B and C (with a space for clarity):

=B2&" "&C2

Then, apply a standard VLOOKUP formula, placing both criteria in the lookup_value, separated by a space:

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

Alternatively, place the criteria in separate cells (G1 and G2) and concatenate them:

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

Since we want data from column D, which is the fourth column in the table array, we use 4 for col_index_num. The range_lookup is set to FALSE for an exact match. The result is shown below:

If your lookup data is on another sheet, include the sheet name in the formula. For example:

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

Alternatively, define a named range (e.g., Orders) to make the formula cleaner:

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

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

Note: For the formula to work correctly, the concatenation in the helper column (B2&" "&C2) must match the format in the lookup_value argument (G1&" "&G2).

Formula 2. Excel VLOOKUP with multiple conditions

While the above method works for two criteria, extending it to more than two has limitations — the lookup value can't exceed 255 characters, and your worksheet layout may not allow a helper column.

Thankfully, Excel offers alternative approaches. You can use INDEX MATCH or the newer XLOOKUP function (available in Office 365) to handle multiple criteria.

For instance, to search based on three values (Date, Customer Name, and Product), use one of these 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 the first criterion (date)
• G2 is the second (customer name)
• G3 is the third (product)
• A2:A11 is the first lookup range (dates)
• B2:B11 is the second (customer names)
• C2:C11 is the third (products)
• D2:D11 is the return range (quantity)

Note: In versions other than Excel 365, enter the INDEX MATCH formula as an array by pressing Ctrl Shift Enter. In Excel 365, it works as a regular formula due to dynamic arrays.

For more details, see:

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

How to use VLOOKUP to get the 2^nd, 3^rd, or n^th match

As you may know, VLOOKUP only returns the first matching value. But what if you need the 2^nd or 3^rd match? Though it seems complex, there are ways to achieve this!

Formula 1. Vlookup the Nth instance

Suppose you have customer names in one column and their purchased products in another, and you want to find the 2^nd or 3^rd product bought by a specific customer.

The easiest way is to add a helper column to the left of your data. This time, populate it with names and occurrence counts like "John Doe1", "John Doe2", etc.

Use COUNTIF with a mixed reference ($B$2:B2) — the absolute part stays fixed, while the relative part expands as the formula is copied down. In row 3, it becomes $B$2:B3, in row 4 it becomes $B$2:B4, and so on.

Combine this with the customer name (B2):

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

Enter this formula in A2 and copy it down.

Next, input the target name and desired occurrence in separate cells (F1 and F2), then use:

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

Formula 2. Vlookup the 2nd occurrence

If you only need the 2nd match, you can skip the helper column. Instead, dynamically create the table array using INDIRECT and 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 bottom-right cell of the table

Note: This formula assumes your data starts in row 2. If your table is elsewhere, use this universal version, where A1 is the header row's top-left cell:

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

How this formula works

The key part is:

INDIRECT("A"&(MATCH(E1, A2:A11, 0) 2)&":B11")

MATCH finds the first occurrence of E1 in A2:A11 and returns its position (3 in our case). We add 2 to skip the first match and the header row. Alternatively, use 1 ROW(A1) to adjust based on the header’s position.

The result is a text string like:

INDIRECT("A"&5&":B11") -> A5:B11

INDIRECT converts this to a valid range, which becomes the table_array for VLOOKUP:

VLOOKUP(E1, A5:B11, 2, FALSE)

How to Vlookup and return multiple values in Excel

VLOOKUP is designed to return only one value. Can it return multiple matches? Yes — but it requires combining INDEX, SMALL, ROW

The above is the detailed content of Advanced VLOOKUP in Excel: multiple, double, nested. For more information, please follow other related articles on the PHP Chinese website!