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

Latest comments

  1. Hi, I have a Salary table with multiple payment. How do I get the Balance after every payment base on particular month salary?

    My Sheet

    Salary
    A1 Month, B1 Salary, C1 Deduction, D1 Balance Salary

    Payment
    F1 March (base on A1 value e.g. March), G1 Payment 1, H1 Balance
    F2 March, G2 Payment 2, H2 Balance

    Thank you if can help me with this

  2. I have an Excel sheet listing documents with various revisions and transmittal numbers used for client sharing. What is the easiest way to find transmittal numbers for documents in different revisions from a large database at once? The same documents are available in multiple revisions.

      1. Is it possible to use index match with multiple criteria to resolve this?

  3. I have a table in sheet1
    Subject ID and subject name
    Columns A and B
    P01 English
    P02 Indonesian
    P03 Mathematics
    ..
    P12 Informatics
    While in sheet2 there is a column of ID code and subject choices in columns A,B,C
    Name. ID. Subject name
    Budi. P07
    Andu. P10
    Bina. P12
    ....etc up to 218 students
    I tried using vlookup but only column C could be filled automatically up to row 22
    What is the solution
    Thank you

  4. Greetings I'm trying to figure out how to get the Metadata (output G3: Date of best time) of those listed in a row (D3:F3) for the minimum value (G4) of the data (D4:F4), so that best times ist listed with the respective date
    D E F G
    3 Date Date Date Date of best time
    4 time time time best time (=min..)
    Any help is much appreciated,
    Vayope

    1. Hi! If I understand your task correctly, you can use INDEX MATCH formula to find the minimum value in row 4 and extract it from row 3. Based on the information given, the formula could be as follows:

      =INDEX(D3:F3, , MATCH(MIN(D4:F4),D4:F4,0))

      You can find the minimum time value with the MIN function.

  5. Hello,

    I am trying to create a formula beyond my knowledge. I am looking to have Column A filled with the number from Column B if the number from Column C is the exact same as a number from anywhere in Column D.

    Column B and C would be paired. I don't know if that explains it well enough, A = B(C = D)

    1. Hello Jordan!
      To find a number from column C in column D, you can use this guide: How to find substring in Excel. If the number was found in the text, use IF formula to write the value from column A to column B.
      Based on this information, the formula could be

      =IF(ISNUMBER(SEARCH(C1, D1)),A1)

      To check for a partial match, you can also use the COUNTIF function, as described in this article: Count cells that contain certain text (partial match):

      =IF(COUNTIF(D1, "*"&C1&"*") > 0,A1)

  6. Hi! Thank you very much, this has been very useful. Can I please have some advice? Please excuse my lack of technical terms, I'm new to Excel. I would like to input a figure (commodity code) into cell D2 and have the formula search for all exact matches in column B (commodity codes for each product on purchase order) once the formula has located each cell containing the commodity code in column B it will add up all matching data in column A and output the total figure into C2?

    FOR EXAMPLE:

    A B C D
    Net Weight Commodity Code Total Net Weight for Specific Commodity Code Specific Commodity Code
    0.36 4202990090 12.6 4202990090
    7.60 4202990090
    0.10 9004909000
    0.08 9004909000
    4.20 4202990090

    Apologies if I haven't explained my question very well.
    If you can please help me with my query I will be very grateful !

    Thanks
    Connor

  7. How to search, compare and match a value from a cell in one template with an instance of a value (not a full cell match) in a row from a second template and return a value from different columns from the second template

    1. Hi! To use a partial match of text strings as searching criteria, use the SEARCH function as described in this guide: How to find substring in Excel. For example:

      =INDEX(B1:B10, MATCH(TRUE, ISNUMBER(SEARCH(LEFT(D1,3),A1:A10)),0))

      I hope my advice will help you solve your task.

      1. It partially helps, but still not what I was looking for.

        My example:

        Template 1

        COLUMN A COLUMN B
        ROW 1: Last Name First Name
        ROW 2: Doe John

        Template 2

        COLUMN A COLUMN B
        ROW 1: Last Name, First Name Date Of Birth
        ROW 2: Doe, John 11/19/1954

        If I wanted to compare values/data from COLUMNS A (Row 2) & B (Row 2) in TEMPLATE 1 with the values/data from ROW 2 in Template 2, and if the match is true for both sets of data (COLUMNS A (Row 2) & B (Row 2) from TEMPLATE 1), to return the data on COLUMN B (Row 2) in Template 2.

        I have used the following formulas, but it will not give me the desired result:

        =IFERROR(INDEX('Template 2'!$B$2, MATCH(TRUE, ISNUMBER(SEARCH(A2, Sheet2!$2:$2)) * ISNUMBER(SEARCH(B2, Sheet2!$2:$2)), 0)), "Not Found")

        =IFERROR(INDEX('Template 2'!$B$2, MATCH(1, (COUNTIF('Template 2'!$2:$2, "*"&A2&"*") > 0) * (COUNTIF('Template 2'!$2:$2, "*"&B2&"*") > 0), 0)), "Not Found")

        Appreciate the help.

        1. Hi! When you multiply two logical values, you get 1 or 0. Try changing the formula

          =IFERROR(INDEX('Template 2'!$B$2, MATCH(1, ISNUMBER(SEARCH(A2, Sheet2!$2:$2)) * ISNUMBER(SEARCH(B2, Sheet2!$2:$2)), 0)), "Not Found")

  8. Hi,
    I am designing a beam on excel, and have data sheets I need to refer to. I need to INDEX match where I am specifying criteria in both rows and columns, here are my criteria:
    "1.00"='Buckling Resistance - UB'!D8:D756
    'Beam Calculations'!AL21=E7:U7
    'Beam Calculations'!J21='Buckling Resistance - UB'!A8:A756
    'Beam Calculations'!K21='Buckling Resistance - UB'!B8:B756
    Thank you in advanced,
    Millie

  9. =INDEX(C3:C46,MATCH(MAX(E3:E46),E3:E46,FALSE),)&" Scored "&MAX(E3:E46)& " in "& "ENGLISH"

    im using this function to find out name of the student who scored maximum marks. if two children got same top score its not showing both names what should i do?

  10. Hi, looking for help to see if this is possible without using =if(and formula. I made it work with that, but exceeded the 8100 characters in a single cell.
    In a table A1:F1 there are 6 random numbers between 1 and 48 that do not repeat
    I would like to be able to match 3 of the 6 numbers in each row and then populate the 3 remaining numbers that go with that set of 6.
    example
    4,9,10,19,25,43
    look up 4,19,25 that would then populate 9,10,43 in another column
    Thanks

      1. Thank you so much for your help and the quick response. This pointed me in the right direction and was able to make this work!! If I wanted to learn this stuff where would be a good place to start? Take care

  11. Hi,
    This works perfectly for pulling out a single number in a data set that I have where I have multiple criteria in my columns & months in the rows (i.e. following your example, as if I had region, vendor & item in the columns, with a list of months in the for the full year).

    Is there a way in which I can continue to do this where I can sum all of the figures that come before a chosen date to get a year-to-date view? For example, if I wanted to know the total sales of Apples by Vendor 1 in the North up to and including September 2024?

    I have tried this with the offset function but quite can't get there with it...

    Thanks in advance!

      1. Hi, I have the same question but my multiple data points that need to be summed are across rows instead of columns. Do I still use a sumif formula for that and if so what does it look like. The link only shows data to be summed in rows not columns.

        1. Hi! It's not clear from your question what data you want to summarize by row. If your criteria are in the column headers, then you specify horizontal row ranges in the growth formula instead of vertical column ranges. For example:

          =SUMIFS(B3:H3, B1:H1, "apples", B2:H2, "Pete")

          If you want to find the sum based on row and column conditions, here is an example formula:

          =SUMPRODUCT(B2:M9*(A2:A9=N1)*(B1:M1=P1))

          Read more: Excel SUMPRODUCT function with multiple criteria.

  12. I need to return the value from within the table below using two different criteria, but the criteria is a number that falls within a number range.
    For example, I have 13skus and 1300boxes, the returning value should be $160.
    The top two rows are the range of box counts for each column (ie. between 501-1000).
    The first two columns are the range for the number of skus.
    How do I set up the formula to return the value of $160 for 13skus and 1300boxes?

    Boxes 01 501 1,001 1,501 2,001
    500 1,000 1,500 2,000 2,500
    Skus
    01-10 $100 $120 $140 $160 $180
    11- 20 $120 $140 $160 $180 $200
    21-30 $140 $160 $180 $200 $220
    31-40 $160 $180 $200 $220 $240

  13. 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*

  14. 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

    1. If I understand the question correctly, you can use the IF function with multiple AND conditions. For example:

      =IF(AND(A1="apple",B1>0),"valid","")

      If you want to detect a partial text match, try this formula:

      =IF(AND(ISNUMBER(SEARCH("apple",A1)),B1>0),"valid","")

      For more information, please read: How to find substring in Excel
      You can also use the MATCH function to determine that at least one row in the range matches the conditions.

      =IF(ISNUMBER(MATCH(1,(ISNUMBER(SEARCH("apple",A1:A10)))*(B1:B10>0),0)),"valid","")

  15. Hi All,

    I am trying to do a index match formula that keeps coming up with the following error: MATCH range must be a single row or a single column.

    My formula is trying to return a specific cell based on it meeting 3 key criteria's:

    1) Year
    2) Type of investment
    3) Template code

    My formula is as follows:

    =INDEX('Detail'!$1:$91,M
    MATCH(0,(P$5='Detail'!$4:$4)*($C6='Detail'!$B:$B)*($P$4='Detail'!$3:$3),0))

    For some further context:

    Detail is the tab it is looking up from.

    1:91 - data range

    P5 - Type of investment

    C6 - Template code

    P4 - Year

    Any help would be much appreciated.

    1. Hi! If you want to find the first match of criteria using MATCH function, use 1 as lookup_value.

      =INDEX('Detail'!$1:$91, MATCH(1,(P$5='Detail'!$4:$4)*($C6='Detail'!$B:$B)*($P$4='Detail'!$3:$3),0))

      I can't check a formula that contains unique references to your data, which I don't have.

  16. Greetings,
    I am having trouble combining the formulas IF and VLOOKUP. I have a table with Weight and Hight of different people. I also have a column with sex and age. I need the table to show me if they are in good weight/height. For sex, they are coded as 1 for male, and 2 for female. In another sheet (on a different tab), I have a growth chart for boys with Stature-for-age and Weight-for-age, wit data that shows in columns: "extremely low weight", "low wight", "normal", "overweight", and "obesity", and age group. I also have a third sheet on another tab with the same information for girls. So I need to enter the data, and the outcome i need is an x on the right column, according to the weight stature data entered, but i need to first consider if the sex data on that cell is 1 (for boys), to look in the table for boys sheet (tab), and if the value is 2, then look on the table for girls; then give me an x if the value is in the range on the chart, so i can see the growth status of this person.
    I also need to find values in other tables for different age groups, but by now ill be satisfied with finding data related to sex on charts for that age group, and their weight stature.
    Your help is highly appreciated!
    Thanks in advance

    1. Hi! If I understand your question correctly, you can use the IF function or the IFS function to do a condition search.
      =IF(A1=1, [INDEX MATCH formula 1], [INDEX MATCH formula 2])
      =IFS(A1=1, [INDEX MATCH formula 1], A1=2, [INDEX MATCH formula 2])

      Maybe this article will be helpful: VLOOKUP with IF statement in Excel

      1. correct.
        =IF(A1=1, look for and find the match values needed on sheet one,
        IF(A1=2, look for and find the match values needed on sheet two,
        if value enteres matches value/rank in selected sheet, then show an "x", otherwise, show " " (nothing)

  17. Is there an INDEX and MATCH formula that can exclude values that match a criterion but does not require the use of control + shift + enter?

    For example, I want a formula to exclude all values that matches to “Beer” but return all other values. I have something in mind like:

    INDEX($B$3:$B$7,MATCH(1,(--(A11=$A$3:$A$7))*(--("Beer"$C$3:$C$7)),0))

    But this formula only works with control + shift + enter. Is there a formula that does not require control + shift + enter?

      1. Thank you. Unfortunately, I don't have a copy of Excel365. I am currently using Excel from Microsoft Home Office 2019. I'm looking for a formula that does not require Ctrl + Shift + Enter since the braces surrounding the formula are easily removed when the array is edited.

          1. Hello, you are not answering the question. I have already explained why an array formula won't work for me. I am looking for a non array formula. But thanks for your time.

            1. The issue you want to solve is unknown to me. But if you need to find values by the criterion, then in your Excel you can do this only with help of array formulas or VLOOKUP function. Other functions are not available to you. I answered your question?

  18. Hi
    I'm using this formula to search the booking number from the table but it won't work for multiple results
    ={INDEX(Table1[Booking],MATCH(1,(ISNUMBER(SEARCH(A1,Table1[Remarks]))*(ISNUMBER(SEARCH(B1,Table1[Remarks])))),0))}
    How can I change these formula to list out all results?

    Thanks!

  19. 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!

      1. Thanks, the suggested formula worked perfectly!

  20. 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!

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)