Excel INDEX MATCH with multiple criteria - formula examples

The tutorial shows how to lookup with multiple criteria in Excel using INDEX and MATCH and a few other ways.

Although Microsoft Excel provides special functions for vertical and horizontal lookup, expert users normally replace them with INDEX MATCH, which is superior to VLOOKUP and HLOOKUP in many ways. Among other things, it can look up two or more criteria in columns and rows. This tutorial explains the syntax and inner mechanics in full detail so that you can easily adjust the formula for your particular needs. To make the examples easier to follow, you are welcome to download our sample workbook.

Excel INDEX MATCH with multiple criteria

When working with large databases, you may sometimes find yourself in a situation when you need to find something but don't have a unique identifier for the search. In this case, lookup with several conditions is the only solution.

To look up a value based on multiple criteria in separate columns, use this generic formula:

{=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))}

Where:

  • Return_range is the range from which to return a value.
  • Criteria1, criteria2, … are the conditions to be met.
  • Range1, range2, … are the ranges on which the corresponding criteria should be tested.

Important note! This is an array formula and it must be completed with Ctrl + Shift + Enter. This will enclose your formula in {curly brackets}, which is a visual sign of an array formula in Excel. Do not try typing the braces manually, that won't work!

The formula is an advanced version of the iconic INDEX MATCH that returns a match based on a single criterion. To evaluate multiple criteria, we use the multiplication operation that works as the AND operator in array formulas. Below, you will find a real-life example and the detailed explanation of the logic.

INDEX MATCH with several criteria - formula example

For this example, we will be using a table in the so-called "flat-file" format with each separate criteria combination (region-month-item in our case) on its own row. Our goal is to retrieve the sales figure for a certain item in a specific region and month.

With the source data and criteria in the following cells:

  • Return_range (sales) - D2:D13
  • Criteria1 (target region) - G1
  • Criteria2 (target month) - G2
  • Criteria3 (target item) - G3
  • Range1 (regions) - A2:A13
  • Range2 (months) - B2:B13
  • Range3 (items) - C2:C13

The formula takes the following shape:

=INDEX(D2:D13, MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

Enter the formula, say in G4, complete it by pressing Ctrl + Shift + Enter and you will get the following result: Excel INDEX MATCH formula with multiple criteria

How this formula works

The trickiest part is the MATCH function, so let's figure it out first:

MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

As you may remember, MATCH(lookup_value, lookup_array, [match_type]) searches for the lookup value in the lookup array and returns the relative position of that value in the array.

In our formula, the arguments are as follows:

  • Lookup_value: 1
  • Lookup_array: (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13)
  • Match_type: 0

The 1st argument is crystal clear - the function searches for the number 1. The 3rd argument set to 0 means an "exact match", i.e. the formula returns the first found value that is exactly equal to the lookup value.

The question is - why do we search for "1"? To get the answer, let's have a closer look at the lookup array where we compare each criterion against the corresponding range: the target region in G1 against all regions (A2:A13), the target month in G2 against all months (B2:B13) and the target item in G3 against all items (C2:C13). An intermediate result is 3 arrays of TRUE and FALSE where TRUE represents values that meet the tested condition. To visualize this, you can select the individual expressions in the formula and press the F9 key to see what each expression evaluates to: How INDEX MATCH with multiple criteria works

The multiplication operation transforms the TRUE and FALSE values into 1's and 0's, respectively:

{1;1;1;1;1;1;0;0;0;0;0;0} * {0;0;1;1;0;0;0;0;1;1;0;0} * {1;0;1;0;1;0;1;0;1;0;1;0}

And because multiplying by 0 always gives 0, the resulting array has 1's only in the rows that meet all the criteria:

{0;0;1;0;0;0;0;0;0;0;0;0}

The above array goes to the lookup_array argument of MATCH. With lookup_value of 1, the function returns the relative position of the row for which all the criteria are TRUE (row 3 in our case). If there are several 1's in the array, the position of the first one is returned.

The number returned by MATCH goes directly to the row_num argument of the INDEX(array, row_num, [column_num]) function:

=INDEX(D2:D13, 3)

And it yields a result of $115, which is the 3rd value in the D2:D13 array.

Non-array INDEX MATCH formula with multiple criteria

The array formula discussed in the previous example works nice for experienced users. But if you are building a formula for someone else and that someone does not know array functions, they may inadvertently break it. For example, a user may click your formula to examine it, and then press Enter instead of Ctrl + Shift + Enter. In such cases, it would be wise to avoid arrays and use a regular formula that is more bulletproof:

INDEX(return_range, MATCH(1, INDEX((criteria1=range1) * (criteria2=range2) * (..), 0, 1), 0))

For our sample dataset, the formula goes as follows:

=INDEX(D2:D13, MATCH(1, INDEX((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0, 1), 0)) Non-array INDEX MATCH formula with multiple criteria

How this formula works

As the INDEX function can process arrays natively, we add another INDEX to handle the array of 1's and 0's that is created by multiplying two or more TRUE/FALSE arrays. The second INDEX is configured with 0 row_num argument for the formula to return the entire column array rather than a single value. Since it's a one-column array anyway, we can safely supply 1 for column_num:

INDEX({0;0;1;0;0;0;0;0;0;0;0;0}, 0, 1) returns {0;0;1;0;0;0;0;0;0;0;0;0}

This array is passed to the MATCH function:

MATCH(1, {0;0;1;0;0;0;0;0;0;0;0;0}, 0)

MATCH finds the row number for which all the criteria are TRUE (more precisely, the the relative position of that row in the specified array) and passes that number to the row_num argument of the first INDEX:

=INDEX(D2:D13, 3)

INDEX MATCH with multiple criteria in rows and columns

This example shows how to perform lookup by testing two or more criteria in rows and columns. In fact, it's a more complex case of the so-called "matrix lookup" or "two-way lookup" with more than one header row.

Here's the generic INDEX MATCH formula with multiple criteria in rows and columns:

{=INDEX(table_array, MATCH(vlookup_value, lookup_column, 0), MATCH(hlookup_value1 & hlookup_value2, lookup_row1 & lookup_row2, 0))}

Where:

Table_array - the map or area to search within, i.e. all data values excluding column and rows headers.

Vlookup_value - the value you are looking for vertically in a column.

Lookup_column - the column range to search in, usually the row headers.

Hlookup_value1, hlookup_value2, … - the values you are looking for horizontally in rows.

Lookup_row1, lookup_row2, … - the row ranges to search in, usually the column headers.

Important note! For the formula to work correctly, it must be entered as an array formula with Ctrl + Shift + Enter.

It is a variation of the classic two-way lookup formula that searches for a value at the intersection of a certain row and column. The difference is that you concatenate several hlookup values and ranges to evaluate multiple column headers. To better understand the logic, please consider the following example.

Matrix lookup with multiple criteria - formula example

In the sample table below, we'll be searching for a value based on the row headers (Items) and 2 column headers (Regions and Vendors). To make the formula easier to build, let's first define all the criteria and ranges:

  • Table_array - B3:E4
  • Vlookup_value (target item) - H1
  • Lookup_column (Row headers: items) - A3:A4
  • Hlookup_value1 (target region) - H2
  • Hlookup_value2 (target vendor) - H3
  • Lookup_row1 (Column headers 1: regions) - B1:E1
  • Lookup_row2 (Column headers 2: vendors) - B2:E2

And now, supply the arguments into the generic formula explained above, and you will get this result:

=INDEX(B3:E5, MATCH(H1,A3:A5,0), MATCH(H2&H3,B1:E1&B2:E2,0))

Remember to complete the formula by pressing the Ctrl + Shift + Enter shortcut, and your matrix lookup with multiple criteria will be done successfully: INDEX MATCH with multiple criteria in rows and columns

How this formula works

As we are searching vertically and horizontally, we need to supply both the row and column numbers for the INDEX(array, row_num, column_num) function.

Row_num is delivered by MATCH(H1, A3:A5, 0) that compares the target item (Apples) in H1 against the row headers in A3:A5. This gives a result of 1 because "Apples" is the 1st item in the specified range.

Column_num is worked out by concatenating 2 lookup values and 2 lookup arrays: MATCH(H2&H3, B1:E1&B2:E2, 0))

The key factor for success is that the lookup values should match the column headers exactly and be concatenated in the same order. To visualize this, select the first two arguments in the MATCH formula, press F9, and you will see what each argument evaluates to:

MATCH("NorthVendor 2", {"NorthVendor 1", "NorthVendor 2", "SouthVendor 1", "SouthVendor 2"}, 0)

As "NorthVendor 2" is the second element in the array, the function returns 2.

At this point, our lengthy two-dimensional INDEX MATCH formula transforms into this simple one:

=INDEX(B3:E5, 1, 2)

And returns a value at the intersection of the 1st row and 2nd column in the range B3:E5, which is the value in the cell C3.

That's how to look up multiple criteria in Excel. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Excel INDEX MATCH multiple criteria (.xlsx file)

How to look up several conditions in Excel

Table of contents

241 comments

  1. I have 3 columns of data having (thickness),(grade) & (speed) along with tons/hr matrix based on different widths(horizontal).
    how do i make a index..match criteria to select tons/hr for given thk, grade, speed and width

    Width ton/h
    thk grade speed 900 1000 1050 1100 1200 1250 1300 1350
    110 lc 6 247 329 245 360 462 432 470 476
    110 mc 5.8 256 329 245 360 473 470 470 462
    112 mc 6 297 329 245 360 450 432 432 476
    114 lc 6.1 280 329 245 360 432 470 470 462
    114 hc 6 301 329 245 360 470 470 432 476
    117 ap 5.5 297 329 245 360 432 470 462 476
    124 gh 4.7 297 329 245 360 470 470 470 462

  2. Hi,
    I have this data special number and total claim(money).
    How I want to pull data total claim (money) but ignore zero because some of the special no has duplicate?

    SPECIAL no Total claim
    231362 48.36
    231361 0
    231361 4200.83
    231363 852
    231364 54.4
    231281 21.92
    231327 21.92
    231328 21.92

    • Hi! I'm not quite clear what you want to count. If you are counting the sum, then zero is irrelevant. Explain in detail what result you want. You can find the sum for each special number using the SUMIF function.

      • Thank you very much! That solved my problem! It's really simple solution.

  3. How can I get "column1" and "column2" from the first three column? I need to get latest "etd date" and itsitem names of "modal". Also, if etd date has the value of "11/11/2000", I need to know in a same cell like column 1 an 2.

    Modal Item name etd date column1 column2
    1 x 11/11/2000 1 (y 20/01/2021 + x no etd)
    1 y 20/01/2024 2 (x 10/12/2023)
    1 z 10/01/2024 3 (a,b,c 09/01/2024 + e no etd)
    2 x 10/12/2023 4 (b 20/01/2024)
    2 b 09/12/2023
    3 a 09/12/2023
    3 b 09/01/2024
    3 c 09/01/2024
    3 y 09/01/2024
    3 e 11/11/2000
    4 a 10/01/2024
    4 b 20/01/2024

  4. IF any duplicate row will given in this example, and i want to add all same row, then what formula will required?

  5. This page helped to be able to pull one value from a table, but how could I modify to concatenate and pull all values that meet the criteria into one cell?

    *doing a two criteria index match based on a name and "yes" in two different columns. Need it to return all values with this name and "yes", not just the first one*

  6. Column A Column B
    5715363263461 271F0345028709
    5715363263461 271F0345028890

    Need a formula where in if i put column a value in column c then i get 2 different values in column d

  7. Can I combined partial text match and if greater than zero match and return with text?

    Example:
    Column A:
    Row1: Apple
    Row2: Banana
    Row3: Grapes

    Column B:
    Row1: $100
    Row2: $0
    Row3: $1

    Formula: match if "apple", >1, return "valid"

    Answer: Valid

    Is this possible?

    Thank you

  8. I having issues figure this out. Below is a sample of data. Here the logic I want to apply

    1) For the first row match to a row that date equals month +1 and that has the same division number. Example 2022 07 match 2202 08 and division are both 1001.
    2) subtract count 172 - 64 = 108

    Any suggest for how to do this?

    Division Date Year Month count
    1001 2022 07 2022 July 172
    1002 2022 07 2022 July 3
    1003 2022 07 2022 July 1
    1005 2022 07 2022 July 7
    1006 2022 07 2022 July 27
    1007 2022 07 2022 July 33
    1009 2022 07 2022 July 1
    1001 2022 08 2022 August 64
    1002 2022 08 2022 September 22
    1003 2022 08 2022 October 11
    1005 2022 08 2022 November 40
    1006 2022 08 2022 December 1214
    1007 2022 08 2022 January 76
    1009 2022 08 2022 February 9

    • Hi! I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Try this formula:

      =F1-INDEX(F1:F14, MATCH(1,(A1=$A$1:$A$14)*(B1=$B$1:$B$14)*((C1+1)=$C$1:$C$14),0))

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)