In this **Excel VLookup multiple criteria (with INDEX MATCH) Tutorial**, you learn how to carry out a VLookup for multiple criteria, with the following constructs:

- An array formula.
- The INDEX function.
- The MATCH function.

This Excel VLookup multiple criteria (with INDEX MATCH) Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process below. **You can get this example workbook (for free) by clicking the button below**.

Use the following Table of Contents to navigate to the Section you're interested in.

## Related Excel VLookup Tutorials

This Excel VLookup multiple criteria (with INDEX MATCH) Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

- Excel VLOOKUP (under development).
- Excel VLookup Multiple Criteria with XLOOKUP in 2 Easy Steps.

## The VLookup Multiple Criteria (with INDEX MATCH) Formula Template/Structure

The following is the VLookup multiple criteria (with INDEX MATCH) formula template/structure I explain (step-by-step) in the Sections below.

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-index-match/
=INDEX(ColumnWithValueToReturn,MATCH(1,(Criterion1=ColumnWithCriterion1)*(Criterion2=ColumnWithCriterion2)*(Criterion3=ColumnWithCriterion3)*…*(Criterion#=ColumnWithCriterion#),0))
```

This is an array formula. If you're not working with Excel 365, you may have to enter this formula by pressing “Ctrl + Shift + Enter”. In these cases, Excel wraps the formula in curly braces ({}).

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-index-match/
{=INDEX(ColumnWithValueToReturn,MATCH(1,(Criterion1=ColumnWithCriterion1)*(Criterion2=ColumnWithCriterion2)*(Criterion3=ColumnWithCriterion3)*…*(Criterion#=ColumnWithCriterion#),0))}
```

## The Example Before VLookup Multiple Criteria (with INDEX MATCH)

This Excel VLookup multiple criteria (with INDEX MATCH) Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process below. **You can get this example workbook (for free) by clicking the button below**.

The example worksheet has 2 tables with the following characteristics.

**(1) Table 1 (Cells A7 to G27).**

The table:

- With the data.
- Where you search with the VLookup multiple criteria (with INDEX MATCH) formula.

Main characteristics:

- 7 columns (Salesperson, Date, Customer, Product, City, Units, Unit Price).
- 1 header row (row 7).
- 20 entries (rows 8 to 27).

**(2) Table 2 (Cells I7 to L14).**

The table where you:

- Set up the VLookup multiple criteria (with INDEX MATCH) formulas.
- Display the results.

This table has the following 4 columns:

- Column: Cells I8 to I14 store letters. These letters match the columns of Table 1.
- Header: The values in cells J8 to J14 match the headers of Table 1 (cells A7 to G7).
- Value:
- Cells K8 and K9: The 2 (multiple) VLookup criteria.
- Cells K10 to K14:
- Currently empty.
- Will store the VLookup multiple criteria (with INDEX MATCH) formulas.

- Formula: Cells L10 to L14:
- Currently display the #N/A error.
- Will display the VLookup multiple criteria (with INDEX MATCH) formulas I enter in cells K10 to K14.

The following image displays the example worksheet before I add the VLookup multiple criteria (with INDEX MATCH) formulas.

## Step 1: Set Up the Multiple Conditions

Create an expression that does the following:

- Evaluate multiple conditions.
- Return a single value for each row (in the table where you search with the VLookup multiple criteria formula), indicating whether all conditions are met (or not) in that specific row.

To do this inside a VLookup multiple criteria (with INDEX MATCH) formula, you work with logical expressions. Logical expressions

- Return a Boolean value (TRUE or FALSE) when evaluated.
- Allow you to test whether a set of multiple conditions is met.

Use the following template/structure to create the expression testing the multiple conditions:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-index-match/
(Criterion1=ColumnWithCriterion1)*(Criterion2=ColumnWithCriterion2)*(Criterion3=ColumnWithCriterion3)*…*(Criterion#=ColumnWithCriterion#)
```

This expression template/structure works with the following 2 operators to test whether a set of multiple conditions is met:

- The equal to comparison operator: =.
- Compares 2 expressions and returns a Boolean value (TRUE or FALSE) as follows:
- TRUE if the 2 expressions are equal (Expression1=Expression2).
- FALSE if the 2 expressions aren't equal (Expression1<>Expression2).

- Compares 2 expressions and returns a Boolean value (TRUE or FALSE) as follows:
- The multiplication operator: *.
- As a general rule, multiplies 2 numbers.
- When multiplying Boolean values (TRUE and FALSE), converts the Boolean values into 1s and 0s as follows:
- TRUE is converted to 1.
- FALSE is converted to 0.

- When creating a VLookup multiple criteria (with INDEX MATCH) formula, the multiplication operator (*) acts as the AND logical operator. In other words: The entire expression returns 1 (TRUE) or 0 (FALSE) as follows:
- 1 (TRUE) if all evaluated conditions return 1 (TRUE).
- 0 (FALSE) if any (or all) evaluated condition(s) return 0 (FALSE).

Consider the following when specifying the expression testing the multiple conditions:

- Carry out as many equal to comparison tests (with the equal to operator; =) as needed (1 equal to comparison test per criterion) to compare each of the multiple criteria against the cells in a column (with the respective criterion).
- Specify the column with a criterion (ColumnWithCriterion#) as a single-column (1 column, several rows) cell range.

In the VLookup multiple criteria (with INDEX MATCH) formula, the expression template/structure I describe above returns an array of 1s and 0s, by applying the 2 operators above in the following order of precedence:

- First:
- The equal to comparison operator (=) is evaluated.
- Each expression with the equal to comparison operator (=) returns an array with TRUEs and FALSEs. Each individual TRUE or FALSE indicates whether the applicable criterion (Criterion#) is met by the applicable row in the applicable column (ColumnWithCriterion#).

- Second:
- The multiplication operator (*) is evaluated.
- The entire expression with the multiplication operator(s) (*) returns an array with 1s (TRUE) and 0s (FALSE). Each individual 1 (TRUE) or 0 (FALSE) indicates whether all applicable criteria are met by all applicable columns in the applicable row.

### Step 1 Example

In the example, 2 conditions must be met:

- Condition 1: The Product (in column D) is equal to the product specified in cell K8.
- Condition 2: The City (in column E) is equal to the city specified in cell K9.

When implementing the template/structure I describe above:

- Criterion1: Cell K8.
- ColumnWithCriterion1: Column D (cells D8 to D27).
- Criterion2: Cell K9.
- ColumnWithCriterion2: Column E (cells E8 to E27).

The resulting expression (using absolute references) is:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-index-match/
($K$8=$D$8:$D$27)*($K$9=$E$8:$E$27)
```

The image below illustrates how the array of 1s and 0s (I mention above) is returned by this expression:

- Columns H (Criterion1) and I (Criterion2) return TRUE or FALSE, depending on whether the criterion (Criterion#) is met by the applicable row in the applicable column (column D for Criterion1, column E for Criterion2).
- Column J (Criterion1*Criterion2) returns 0 or 1, depending on whether all criteria (Criterion1 and Criterion2) are met by all applicable columns (column D and column E) in the applicable row.
- Column K (Step 1 Array):
- Uses the basic structure of the expression testing the multiple conditions (I describe above).
- Returns 0 or 1, depending on whether all criteria (Criterion1 and Criterion2) are met by all applicable columns (column D and column E). These results are exactly the same as those returned by the (illustrative) process carried out in columns H, I, and J.

- Cell L8 displays the formula (using the basic structure of the expression testing the multiple conditions) stored in column K.

The 2 (multiple) conditions are met in row 24 of the worksheet.

## Step 2: Use the MATCH Function to Find the Row Where the Multiple Conditions are Met

In the VLookup multiple criteria (with INDEX MATCH) formula, the MATCH function does the following:

- Search for an item in an array; and
- Return the relative position of the item in the array.

Specify MATCH's arguments as follows to find the row where the multiple conditions are met:

- lookup_value: 1.
- lookup_array: The expression (returning an array of 0s and 1s) you created in step #1.
- match_type: 0.

The resulting template/structure is as follows:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-index-match/
MATCH(1,ArrayFromStep1,0)
```

When considering the array you created in step #1:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-index-match/
MATCH(1,(Criterion1=ColumnWithCriterion1)*(Criterion2=ColumnWithCriterion2)*(Criterion3=ColumnWithCriterion3)*…*(Criterion#=ColumnWithCriterion#),0)
```

Consider the following when setting up the MATCH function inside the VLookup multiple criteria (with INDEX MATCH) formula:

- As a general rule, if the criteria columns you work with (to specify the array of 0s and 1s you created in step #1) have duplicate values (several rows match the multiple criteria, resulting in several rows matching the lookup_value of 1), the MATCH function works with the first entry matching the lookup_value (1).
- The MATCH function is case insensitive (when matching text values).
- If the MATCH function doesn't find an exact match for the lookup_value (1), it returns the #N/A error.

### Step 2 Example

In the example, the MATCH function (using absolute references) is as follows:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-index-match/
MATCH(1,($K$8=$D$8:$D$27)*($K$9=$E$8:$E$27),0)
```

The image below displays the relative position of the (sought) number 1 (row number inside the table where you search with the VLookup multiple criteria formula where all criteria are met) in the array of 0s and 1s (you created in step #1).

- Cells L10 to L14 display the position of the (sought) number 1 in the array of 0s and 1s.
- Cells M10 to M14 display the function template/structure I explain above (with MATCH), as used in cells L10 to L14.
- Column H displays the relative position of each row in the array.

The relative position of row 24 (where the multiple conditions are met, as you found in step #2) in the array is 17.

## Step 3: Use the INDEX Function to Extract the Value in the Row Where the Multiple Conditions are Met

In the VLookup multiple criteria (with INDEX MATCH) formula, the INDEX function returns the value in a specific row in a single-column (1 column, several rows) cell range.

Specify INDEX's arguments as follows to obtain the value the VLookup multiple criteria (with INDEX MATCH) formula returns:

- array: The single-column (1 column, several rows) cell range (in the table where you search with the VLookup multiple criteria formula) with the value to return.
- row_num: The MATCH formula you created in step #2.

The resulting formula template/structure is as follows:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-index-match/
=INDEX(ColumnWithValueToReturn,MATCH(1,ArrayFromStep1,0))
```

When considering the full MATCH function you created in step #2:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-index-match/
=INDEX(ColumnWithValueToReturn,MATCH(1,(Criterion1=ColumnWithCriterion1)*(Criterion2=ColumnWithCriterion2)*(Criterion3=ColumnWithCriterion3)*…*(Criterion#=ColumnWithCriterion#),0))
```

### Step 3 Example

In the example, the formulas (using absolute references) are as follows:

- Cell K10:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-index-match/
=INDEX(A$8:A$27,MATCH(1,($K$8=$D$8:$D$27)*($K$9=$E$8:$E$27),0))
```

- Cell K11:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-index-match/
=INDEX(B$8:B$27,MATCH(1,($K$8=$D$8:$D$27)*($K$9=$E$8:$E$27),0))
```

- Cell K12:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-index-match/
=INDEX(C$8:C$27,MATCH(1,($K$8=$D$8:$D$27)*($K$9=$E$8:$E$27),0))
```

- Cell K13:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-index-match/
=INDEX(F$8:F$27,MATCH(1,($K$8=$D$8:$D$27)*($K$9=$E$8:$E$27),0))
```

- Cell K14:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-index-match/
=INDEX(G$8:G$27,MATCH(1,($K$8=$D$8:$D$27)*($K$9=$E$8:$E$27),0))
```

The formulas above differ (exclusively) on the column specified as array argument of the INDEX function. That's the column (in the table where you search with the VLookup multiple criteria formula) with the value to return.

- Cell K10: Returns a value from column A (Salesperson).
- Cell K11: Returns a value from column B (Date).
- Cell K12: Returns a value from column C (Customer).
- Cell K13: Returns a value from column F (Units).
- Cell K14: Returns a value from column G (Unit Price).

The image below displays the final results returned by the VLookup multiple criteria (with INDEX MATCH) formula.

- Cells K10 to K14 display the value returned by the applicable VLookup multiple criteria (with INDEX MATCH) formula, as follows:
- Cell K10: The value in cell A24 (Salesperson 50).
- Cell K11: The value in cell B24 (22-May-19).
- Cell K12: The value in cell C24 (Customer 5).
- Cell K13: The value in cell F24 (84.00).
- Cell K14: The value in cell G24 (499.99).

- Cells L10 to L14 display the example VLookup multiple criteria (with INDEX MATCH) formula, as used in cells K10 to K14.

## Step 4: Enter the Formula as an Array Formula

The VLookup multiple criteria (with INDEX MATCH) formula template/structure you learned in this Tutorial is an array formula.

If you're not working with Excel 365, you may have to enter this formula by pressing “Ctrl + Shift + Enter”. In these cases, Excel wraps the formula in curly braces ({}).

### Step 4 Example

The image below displays the formulas wrapped in curly braces ({}) if I press “Ctrl + Shift + Enter” to enter them. The results are the same as those displayed step #3.

- Cells K10 to K14 display the value returned by the applicable VLookup multiple criteria (with INDEX MATCH) formula, as follows:
- Cell K10: The value in cell A24 (Salesperson 50).
- Cell K11: The value in cell B24 (22-May-19).
- Cell K12: The value in cell C24 (Customer 5).
- Cell K13: The value in cell F24 (84.00).
- Cell K14: The value in cell G24 (499.99).

- Cells L10 to L14 display the example VLookup multiple criteria (with INDEX MATCH) formula, as used in cells K10 to K14.

## Download the VLookup Multiple Criteria (with INDEX MATCH) Example Workbook

This Excel VLookup multiple criteria (with INDEX MATCH) Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process above. **You can get this example workbook (for free) by clicking the button below**.

## Related Excel VLookup Tutorials

This Excel VLookup multiple criteria (with INDEX MATCH) Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

- Excel VLOOKUP (under development).
- Excel VLookup Multiple Criteria with XLOOKUP in 2 Easy Steps.