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.

Tip. In Excel 365 and 2021, you can use XLOOKUP formula with multiple criteria.

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

291 comments

  1. Hi,

    I have a spreadsheet where I need to get the value (string of text) in column A that is in the cell to the left and 1 down from a non-blank value in column B. So, the set up of the spreadsheet has headers in row 1, then rows A2-A4 list strings and B2-B3 are empty (blank) then B4 has a number, repeat with A5-A7 strings and B5-B6 empty then B7 a number, . . .. What I need is the string from A2, then A5, then A8, . . .. I've tried some formulas with index, match, isblank, and offset, but I'm still too novice to excel to get this figured out quickly.

    Thanks for your help!

      • Thanks, the suggested formula worked perfectly!

  2. Hello,

    I need to come up with the index match formula to get the data from my large data sheet. I have a table in excel with the same questions but different answers and comment for each state, I need to pull all the data into the tabular display. I want to click o the cell and type e.g. Oklahoma and all my data will be pulled into my new sheet. Does anyone have idea how to do this?

    Thank you in advance!

  3. Is it possible to use your methods to utilize OR/AND functions? In my data I have 3 criteria to compare:

    Table1:

    Column A: Name
    Column B: Question 1 (Yes/No)
    Column C: Question 2 (Yes/No)
    Column D: Question 3 (Yes/"Blank")

    The goal is to list all the rows where the answers to EITHER question 1 -OR- 2 is "Yes" -AND- the answer to question 3 is "Yes".
    As a result, any row where question 3 is blank would be excluded, and any rows where BOTH questions 1 and 2 are "No" are also excluded.

  4. Hi,

    How would you write the "Matrix lookup with multiple criteria"- specifically the part "MATCH(H2&H3,B1:E1&B2:E2) using the indirect function?

    I was typing it as ,MATCH($A$4&"*"&$A63&"*",INDIRECT("'"&A$1&"'!$A:$A")&INDIRECT("'"&A$1&"'!$B:$B")
    but it keeps coming up as an error

    thank you

      • Hi,

        Thank you for your reply, sorry I haven't been clear. So, it's already a two dimensional INDEX MATCH MATCH function as I'm working on a cashflow, where there are two columns of criteria and one row. So at the moment it is working fine like this:

        =IFERROR(INDEX('Sheet1'!$A:$Z,MATCH($A$4&"*"&$A32&"*",'Sheet1'!$A:$A&'Sheet1'!$B:$B,0),MATCH(F$2,'Sheet1'!$A$1:$Z$1,0)),0)

        However, I would like to make it more dynamic by using an indirect function for 'Sheet1'!', Is this possible?

  5. thank you in advance.
    what will be my formula if I want to get the column header starting from given criteria.
    Example:
    01-Jul 02-Jul 03-Jul 04-Jul 05-Jul 06-Jul 07-Jul 08-Jul 09-Jul 10-Jul
    A 1 1 1 1 1 R R R 1 1

    i want to get the end date of my training given that I have 4 days duration (not including restday R).
    start date would be 04-Jul.
    what would be my formula if i want to start counting the 4 days from 04-Jul, result should be 10-Jul.

    thank you

  6. Great alternative to nested IFs. How do I make the formula, from your example, contain "and/or" criteria rather than purely having "and" criteria:

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

  7. Just a quick note to thank you for the how-to! Though I am slowly picking up cool tricks in Excel I doubt I would have ever figured this out on my own. Had to tweak to be able to drag formula down multiple cells but managed to match up data from multi spreadsheets with thousands of rows. Just did =INDEX(Sheet9!C:C, MATCH(1, INDEX((A2=Sheet9!A:A) * (B2=Sheet9!B:B), 0, 1), 0)) which was scary spinning wheel slow and sure there is cleaner ways to do but it worked. Much appreciate!!

  8. How would I write a formula that is capable of checking across multiple sheets to find a specific date and then pull the data from the particular sheet that the date appears on?

    I have searched high and low through numerous sites and forums and have not been able to find anything that fits the bill.

    I am using Excel 2010. The data being pulled is just numbers to one decimal point.

    What I have.
    The first sheet (Last 31 Days) is where I am pulling the data to. I have a formula that updates the dates shown each day so that the individual cells in column B show the last 31 days.
    The second sheet (2022) contains data for 2022 separated into each month (actual and dummy data).
    The third sheet (2023) contains data for 2023 separated into each month (dummy data).
    All 3 sheets are exactly the same format and layout.

    What I would like the formula to do is to check the "2022" sheet for the required date and pull the data from that sheet if found, but if it does not find the required date, then moves to the "2023" sheet to find the required date and pull the data from that sheet if found.

    Formulas I have that work individually.
    =INDEX('2022'!$C$3:$C$389, MATCH(B3, '2022'!$B$3:$B$389, 0))
    =INDEX('2023'!$C$3:$C$389, MATCH(B3, '2023'!$B$3:$B$389, 0))

    B3 - the cell that contains the date on the "Last 31 Days" sheet.
    '2022'!$B$3:$B$389 - the cell range that the date is being searched on the "2022" sheet.
    '2022'!$C$3:$C$389 - the cell range that the data is being pulled from on the "2022" sheet.
    '2023'!$B$3:$B$389 - the cell range that the date is being searched on the "2023" sheet.
    '2023'!$C$3:$C$389 - the cell range that the data is being pulled from on the "2023" sheet.

    Thank you in advance.

      • I need to count the number of sales orders where the pallet quantity is greater than 21. The same sales order number can appear on multiple rows, and the pallet count is tied to each line. In my formula it isn't summing up pallet quantity if a sales order appears multiple times. Assuming I need some sort of combo of count and sum?

        Criteria: Count # of sales orders where (Customer = Mike) (Sum of Pallet quantity >=22) (Package Type = Case)

        Desired result: 2

        Current Formula =COUNT(FILTER(SalesOrderNumber!A:A,( (Package Type!C:C="Case")*(Customer!E:E="Mike")*(Pallet Quantity!D:D>=22) )))

        SalesOrderNumber ItemNumber Package Type Pallet Quantity Customer
        16590 14590056 Drum 23 Amy
        17950 14590033 Case 23 Mike
        17806 33202332 Case 10 Mike
        18900 33202332 Case 11 Mike
        18900 33202332 Case 11 Mike

        • Hello!
          According to the data and conditions you specified, the result cannot be equal to 2. Here is the SUMPRODUCT formula for calculating according to your criteria

          =SUMPRODUCT(--(E2:E6="Mike"),--(D2:D6>=22),--(C2:C6="Case"))

          Result = 1.

  9. Thank you for this great article! Is there a way to pull data from a different column if the formula wasn't able to find anything on the original column? ie. if 'Device List'!E2:E190=0, pull data from 'Device List'!F2:F190 instead?

    =INDEX('Device List'!E2:E190,MATCH('User Details'!A2&'User Details'!D2,'Device List'!H2:H190&'Device List'!K2:K190,0))

    Thank you!

  10. Thank you, Alexander! This was exactly what I needed to be able to select data from one of multiple columns based on two values in the column headers.

    Happy 2022 to you Ablebits team!

  11. I have the following formula:

    =IFERROR(INDEX(DATA!$A$2:$A$100,SMALL(IF(DATA!$D$2:$D$100"",ROW(DATA!$A$2:$A$100)-ROW(DATA!$A$2)+1,""),ROW(1:1))),"")

    This finds the first entry from column A that does not have a blank entry in column D and returns the value before moving to the next row.

    What I would like to do is add a second check into this based on 4 letters (such as "ABCD" or "TFSF") and for some reason I can't get the INDEX MATCH to work correctly.

    The DATA sheet is an separate indexed sheet that parses data from a third sheet with merged cells.

    Basically, find first number with blank in in column D in the DATA sheet that also has ABCD in Column C then go to the next row.

    thanks for any help on this!

    • Hi!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Unfortunately, without seeing your data it is difficult to give you any advice.

  12. Is it possible to use match for partial matches in a string in this kind of formula? for example i would want to match for a cell that matches an "ABC" into an array that would have a cell matching with ABC-XXXX. So the match is only partial. I tried using &"*" and they are giving me #ref

    thank you so much.

  13. How does the formula work with multiple conditions on both X and Y matches?

    • Hi!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

  14. Hi Alex,
    I am trying to apply the formula in the example above but with additional criteria of selecting the maximum value. For instance, If C5 is replaced with Apples, what formula would give G4 as $130

  15. Hello

    I am having trouble applying this. I am trying to match a row of data from one sheet to match the exact set of variables from another sheet and pick up the 8th entry associated with it, the order amount.

    For example I want to find the entry that matches exactly these details from sheet1 in sheet 2 and where they match exactly pick up the order amount indicated in sheet 2. These are columns and rows. So if the entry I am looking at is "Sheet1!A3:G3" and looking for this exact entry in "Sheet2!"A1:G1000". Lets say they match on row 15 of sheet 2 then I want to pick up the entry in "Sheet2!H15

    Sheet1 1 entry
    product, weight, type, grower, supplier, available, price, order
    zucchini, 8kg, box, organic, vikram, yes, 30, (information needed)

    Sheet 2
    product, weight, type, grower, supplier, available, price, order
    zucchini, 8kg, box, organic, vikram, yes, 30, 3

    So I want to pick up the order amount of 3 from Sheet2 using this approach.

    Ive tried but am not getting it right. Could you please help me?

    Thanks very much,

    Warm regards

    Nicholas

    • Hello!
      You can use something like this:

      =INDEX('Sheet1 (2)'!H2:H10,MATCH(1,(Sheet1!A2='Sheet1 (2)'!A2:A10)*(Sheet1!B2='Sheet1 (2)'!B2:B10),0))

      I only used 2 conditions. You can find examples and detailed instructions in this article above.
      I hope it’ll be helpful.

  16. Hi Alexander,

    This article has been super helpful for a novice like myself! I have a question, I'm trying to create a formula for determining the shortest distance between two zip codes and I have the table set up in a matrix. How would I go about pulling in the lowest number with the table looking like this below?
    For 21607, 21640, 21864 - I would need a formula to determine which number in each row is the lowest. Thank you in advance.

    12110 87110 18034 50010
    21607 272 374 100 950
    21640 274 522 105 955
    21864 336 487 170 999

  17. The Matrix lookup with multiple criteria - formula example really helped me a lot. However, I also want to get the sum of multiple cells based from the criteria. How can i incorporate to get the sum of values? or should i use sumifs instead? Hope to hear from you the soonest. Thanks!

      • Here are my criteria:
        1. Interval: from Interval 1 to 288
        2. Plant: Plant 1, 2, 3 (this is the criteria I want to sum the values, there are duplicate plants in a day)
        Date:

        Below is the data were I get the values applying Matrix lookup with multiple criteria:

        Interval 7/26/2020 7/26/2020 7/27/2020 7/27/2020 7/28/2020 7/28/2020 7/28/2020
        (every 5mins.) Plant 2 Plant 1 Plant 1 Plant 1 Plant 2 Plant 3 Plant 1
        1 0 0.0517 0 0.0532 0 0 0.0530
        2 0 0.0522 0 0.0521 0 0 0.0532
        3 0 0.0514 0 0.0523 0 0 0.0529
        4 0 0.0519 0 0.0522 0 0 0.0531
        5 0 0.0515 0 0.0518 0 0 0.0532
        .

        .
        288

        I have used the matrix lookup with multiple criteria. However, INDEX is a lookup function and will only ever return a value from a single cell. Though the values are 0, I want to get the sum of the plants for every date and interval..

        Here is my formula:
        =IFERROR(INDEX('Plant A'!$C$6:$BV$293,MATCH('Summary_Plant A'!C6,'Plant A'!$B$6:$B$293,0),MATCH('Summary_Plant A'!B6&'Summary_Plant A'!$D$5,'Plant A'!$C$4:$BV$4&'Plant A'!$C$5:$BV$5,0)),0)

        I did try to add a sum function but the result if not zero (no value), wrong result, #value or no change in the result. How can I incorporate sum/sumif/sumifs function in the formula? I am also wondering if I should use the sumifs function instead of the index-match? need your assistance to create the correct formula. Hope the sample above helps to visualize the task. Thank you.

        • Hello!
          If I understand your task correctly, the following formula should work for you:

          =SUMPRODUCT(B3:H100*(B1:H1=K1)*(B2:H2=L1))

          K1 - date
          L1 - plant

          • Hello,

            The formula worked well. Thank you for your prompt assistance!

  18. I am trying to figure out how to use this to match 2 criteria, with index returning the max value of the second criteria. The data is formatted in 3 columns:

    Column A - Employee name
    Column B - Manager name
    Column C - Date assigned to that manager

    An employee may have multiple records in the table if they were under different managers at different times.

    I want to determine the name of the manager the employee was assigned to on a specific date. So, I want to match the employee's name with Column A, then find the max date that is after my search date - and return the manager's name.

    For example, if my data looked like this:

    Employee A -- Manager Z -- 1/1/2008
    Employee A -- Manager Y -- 8/1/2015
    Employee A -- Manager X -- 11/28/2018

    I want to be able to search for Employee A on an arbitrary date, let's say 12/25/2017 - and get the answer back of Manager Y

    • Although ideally the data would always be arranged in chronological order where the last matching result would be the correct one, I'd like the formula not to depend on that if possible since I'm not the only one entering data in the table!

      • For reference, this is what I have so far, but it's not working:

        EmpTable has 3 columns: Employee -- Manager -- Assigned Date
        My jobs table, where I'm trying to put this formula, has columns for Employee and JobDate

        =INDEX(EmpTable[Manager],MATCH(1,([@Employee]=EmpTable[Employee])*([@JobDate]>=EmpTable[AssignedDate]),0))

        This formula gives me a result of #N/A, even though I'm entering valid search data

      • I am entering it as an array function using Shift + Ctrl + Enter

      • I have tried with the non-array formula as well, but no progress. Still no clue how to select the record with the max assigned date, and the formula is still giving me #N/A

        =INDEX(EmpTable[Manager],MATCH(1,INDEX(([@Employee]=EmpTable[Employee])*([@JobDate]>=EmpTable[AssignedDate]),0,1),0))

  19. Hi, is it possible to drag one of the 1st 2 formulas into other rows of the sheet? I tried it but it seems like it didn't work.

    • I have a column where each cell needs to have the formula applied. It does work on a single cell but not when I try to drag the formula to the other rows of the same column. Thanks again.

  20. great example, thank you for information,
    however can we place more than 3 criterias for the index-match formula? index(match(criteria1),(criteria2),(criteria3),(criteria4),etc

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 :)