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

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

  2. Hello!

    I am using the following: sum(INDEX('[dateland - jackson fuel macro data.xlsm]Jan'!$F:$F,MATCH(1,($X$4='[dateland - jackson fuel macro data.xlsm]Jan'!$E:$E)*($A12='[dateland - jackson fuel macro data.xlsm]Jan'!$C:$C)*($C12='[dateland - jackson fuel macro data.xlsm]Jan'!$D:$D),0)))

    My problem is, it is only summing the first number that matches the criteria, and not adding the multiple rows together where the Inv # & Fuel type match.

    Am I using the incorrect formula?

    I have also tried aggregate and subtotal to no avail.

    Thank you!

    • Hello! It is very difficult to understand a formula that contains unique references to your workbook worksheets. However, note that INDEX MATCH always returns only the first value that matches the search criteria. To get the sum of multiple criteria, use the SUMIFS function or the SUMPRODUCT function.

  3. Hi,

    I am using a modified version of your

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

    I am not using the index portion as i don't need to find a specific index or return any value. I am just using the match portion MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0)) which returns the row that satisfies all of the criteria. This works for me when I paste it into an Excel cell. However, when I try to use the same formula in VBA i get a runtime error Type Mismatch on the lookup_array portion of the formula. each on the parenthesis like (G1=A2:A13) says Type Mismatch in the debugger.

    I know that all of my variables and things are right as it all works correctly when i just use the match function with basic values like (G1, A2:A13,0)

    Is there anyway to get around this?

  4. Hi there,
    Thanks for the share, very practical indeed.
    But I do find that it doesn't work for data extract from different tabs.
    =INDEX('Tab1'!A3:A54;MATCH(1,('Tab2'!D2>='Tab1'!F3:F54)*('Tab2'!D2=F3:F54)*(D2<=G3:G54),0)) then Ctrl+Shift+Enter works.

    Thanks,
    Aimee

  5. Hi, I am trying to match two conditions in a table against some data. What I am trying to do is: If, Column A of Table 1 matches Cell A2 in the raw data AND if, Column B of Table 1 also matches Cell G2 in the raw data, I would like it to return Match or 1. If Both Columns in table 1 does not match the two column in the raw data I would like to to return something else e.g no match, 0, N/A etc. I am just trying to separate the in the raw data, the ones that both match the two columns in the 2 coloums of the raw data.

    E.G
    Table 1:
    A | B |
    -----------------------------------------
    XYZ | ABC |
    ----------------------------------------
    BBB| CBA |
    -----------------------------------------
    RBA | IUW |

    RAW DATA:
    A | B | C | D | E| F |
    -----------------------------------------
    XYZ | 1 | 3 | Z |W| ABC | > This should match as column A and F match a row in table 1
    ----------------------------------------
    BBB| 1 | 3 | Z |W|IUW | > This should not match. Although column A AND F appears in table 1, Table 1 shows them in different rows rather than the same row
    -----------------------------------------
    BBB| 1 | 3 | Z |W|CBA | This should match as column A and F match a row in table 1

    Thank you

    • Hi! If I understand your task correctly, use COUNTIFS function to find matches in two columns of different tables. The formula might look like this:

      =IF(COUNTIFS(Sheet1!A1:A20,Sheet2!A2,Sheet1!B1:B20,Sheet2!F2),"match","not match")

      I recommend paying attention to the Compare Sheets tool. It will help you quickly find and highlight differences between two Excel tables or sheets. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

      • Thank you Alexander for you super swift response!. It worked perfectly.

        I will take a look at the compare sheets tool.

        Thank you!!

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

    • 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

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

  7. Please can you advise me - I'm not an Excel wiz.

    I previously ran a COUNTIFS query to identify the number of instances in my data where five criteria were met (returned 4 instances). I then wanted to know which instances met these five criteria, so I tried applying an INDEX MATCH (multiple criteria) formula. This successfully returned the first instance, but I want to know about the other three. How I do retrieve the second, third, and fourth instances?

    Formula used: {=INDEX(D2:D209, MATCH(1, (AI212=AF2:AF209) * (AI212=AJ2:AJ209) * (AI212=AN2:AN209) * (AI212=AP2:AP209) * (AI212=AS2:AS209), 0))}

    I also tried to using a VLOOKUP (multiple matches in multiple rows) formula (found here: https://www.ablebits.com/office-addins-blog/vlookup-multiple-values-excel/#vlookup-multiple-matches-rows), but the cell(s) shows up empty. Not sure where I'm going wrong (might be the 'm' and 'n', but I have no clue).

    Formula used: {=IFERROR(INDEX(D2:D209, SMALL(IF(1=((--(AI212=AF2:AF209)) * (--(AI212=AJ2:AJ209)) * (--(AI212=AN2:AN209)) * (--(AI212=AP2:AP209)) * (--(AI212=AS2:AS209))), ROW(D2:D209)-212,""), COLUMN()-34)),"")}

    I'd appreciate any guidance you could provide.

      • Thanks for your prompt reply. I tried the suggested FILTER formula, and I was able to find the data I was looking for. Thank you!

        P.S. Just out of curiosity, is it possible to find second, third, nth instances using the first of the two formulae I tried (i.e., INDEX MATCH (multiple criteria))? It would be useful, because the INDEX MATCH formula specifies the return range whereas the FILTER formula pulls all data in the array, when it's only one column that is needed.

  8. Hi, I'm using Excel 2019. I'd like to index the column (L1 to L14) where either "NZ" or "N" or "E" or "RZ' first appears for a given row (1 to 4) - for example, Column L3 for row 1, Column L1 for row 2, column L3 for row 3, and column L5 for row 4. I would like to exclude K, R or Y.

    L1 L2 L3 L4 L5 L6 L7 L8 L9 L10 L11 L12 L13 L14
    1 Y Y NZ NZ NZ NZ N N NZ NZ NZ NZ Y Y
    2 NZ NZ NZ NZ NZ NZ N E E E E E
    3 K K N N NZ NZ Y Y Y Y K K
    4 R R Y Y RZ RZ Y Y RZ RZ N N

    Thank you very much.

    • Hi! Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

      =INDEX(A1:F1,MATCH(1,(A1:F1<>"Y")*(A1:F1<>"K")*(A1:F1<>"R"),0))

      • Thank you very much for your kind help. Mainly, I would like to identify the first column that contains either N, NZ, E or RZ in each row.

          • Hi, thank you for the advice BUT it has not solved the problem yet.

            Let me try to restate the issue:

            L1 L2 L3 L4 L5 L6 L7 L8 L9 L10 L11 L12 L13 L14
            Y Y NZ NZ NZ NZ N N NZ NZ NZ NZ Y Y
            NZ NZ NZ NZ NZ NZ N E E E E E
            K K N N NZ NZ Y Y Y Y K K
            R R Y Y RZ RZ Y Y RZ RZ N N

            The columns are labelled L1 to L14. I would like to index the first column in each row that contains either NZ, N, E or RZ. I have tried using {=INDEX($A$1:$N$1,MATCH(TRUE, ISNUMBER(SEARCH(A3:N3,"NZ")),0))} but it only gives the result for NZ. I would like to use this with multiple conditions to match either NZ, N, E or RZ and index the column that first contains that, within the same equation. Thanks

            • I've also tried {=INDEX($A$1:$N$1, 1, MATCH(TRUE,ISNUMBER(SEARCH({"N","NZ","E","RZ"},A3:N3)),0))} BUT it still gives #N/A for all the rows except for the 2nd row where it correctly identifies L1.

              • Here is one that seems to work BUT it still returns a result even for empty rows.

                {=INDEX($A$1:$N$1, 1, MIN(IF((ISNUMBER(SEARCH("N", A3:N3))) + (ISNUMBER(SEARCH("NZ", A3:N3))) + (ISNUMBER(SEARCH("E", A3:N3))) + (ISNUMBER(SEARCH("RZ", A3:N3))) > 0, COLUMN(A3:N3)-COLUMN(A3)+1, "")))}

                Please advise.

              • To not show anything if no value is found, try this formula:

                =IFERROR(INDEX($A$1:$N$1, 1, MIN(IF((ISNUMBER(SEARCH("N", A3:N3))) + (ISNUMBER(SEARCH("NZ", A3:N3))) + (ISNUMBER(SEARCH("E", A3:N3))) + (ISNUMBER(SEARCH("RZ", A3:N3))) > 0, COLUMN(A3:N3)-COLUMN(A3)+1, -1))),"")

            • Hi! Every new question you ask is different from the previous one. I'm not quite sure what you mean by "index the column". Try using this guide above. The formula might be something like this:
              INDEX(return_range, MATCH(1, INDEX((criteria1=range1) + (criteria2=range2) + (..), 0, 1), 0))

  9. I'm trying to return a specific value but having problems with the formula.

    I would like to return E based on looking up data in Column 1 to 4 but from inputting a one piece of data based on column 1-4 on different sheet.
    Basically, the I would input B but it would look through all the data in Column 1-4 and return E.

    EX.
    Column 1 / Column 2 / Column 3 / Column 4 / Column 5
    A B C D E
    F G H I J

      • I tried to make an example but let's say like this

        I want to put answer in a specific cell based on info like this

        Columns are like this (named A, B, C, etc. for simplicity) and data is in rows below those columns (expressed as numbers)

        A B C D E X
        1 2 3 4 5 Y
        6 7 8 9 10 Z

        I want to have a separate cell if I put a number from columns A to E that it will look through all that data then return the info from in Y or Z. Hopefully this example makes sense. Appreciate the help in advance.

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

          =INDEX(F1:F5,MIN(IF(A2:E5=G1,ROW(A2:A5))))

          • Definitely made progress with that but seems I'm still missing something that I'm trying to accomplish.

            Let me try this example:

            Top Row will be the column names and info will start on next row
            The top row is customer names and they all reference my internal item number (example below: XYZ and WXY is my number)
            The next row would be their personal customer product number number (express as 1, 2, 3.

            Customer A / Customer B / Customer C / Customer D / Product number
            1 / 2 / 3 / 4 / XYZ (our product number)
            5 / 6 / 7 / 8 / WXY (our product number)

            I want enter their product number in a complete different cell that will search all of that data to return our product number.
            Example: If I was to enter 4 then it would return XYZ
            Then if I can take it a step further in a cell beside it to reference which customer it would return (using above example then it would be Customer D

            There will be instances that our product number doesn't reference any of the numbers.

            Essential my two cell with the formulas would return
            I enter in first cell the number 4 then return XYP and in cell beside would say customer D.

            Does this make sense?
            I appreciate the help as I feel really close to getting the correct formula but something keeps throwing it out of whack.

  10. Hi, I have a question

    So I created a table including the origin, destination, shipment type, max weight and price. The shipment type has 2 types: box & pallet, the quantity of box is not included but for the quantity of pallet. the more quantities pallet have, the higher price it will be. I can use index and match to find out the price when I filter the origin & destination on the pivot table, but I can't figure out the pallet one since the quantity limits my imagination.

    For my formula of box one: =INDEX(Table2[PRICE], MATCH(1,(B2=Table2[ORIGIN])*(C2=Table2[DES]),0))

    Could you please assist me on the pallet one? It's better if you can combine these 2 formulas together, like writing a ifs statement.

    Thank you!

    • Hi! Sorry, I do not fully understand the task. Unfortunately, this information is not enough to understand what you need. To understand what you want to do, give an example of the source data and the desired result.

  11. Hi

    I am struggling with an index and match

    The table of data would look a bit like the below

    I need to look up the data in table 1, lookup the data in column A and column B, look across the rows in table 2, from column C-E to find corresponding data from Column A table 1, then look at the value in table 1 column B, check if it is in between the range of min and max , and return the value corresponding to the value under column C-E for the data type within column A in table 1, and return the value to column C in table 1.

    Table 1 sheet 1is the input table, table 1 sheet 2 is the data table

    All help appreciated. thanks

    Table 1 (Sheet1)
    Column A Column B column C
    Nike 5 5.5
    NB 11 1.1

    Table 1(Sheet2)
    Column A Column B Column C Column D Column E
    Range Min Range Max Nike NB Adidas
    1 10 5.5 4.2 3.9
    11 20 3 .1 1.1 2.2

    • Sorry, but we cannot offer assistance beyond the scope of this blog. The solution to your query requires a more comprehensive approach and cannot be addressed through a one formula. However, if you have a specific question about a function or formula, feel free to ask, and we will do our best to assist you. Also describe the result you want to get.

  12. Hello
    Can you help me to make consolidate of two different sheets data by using the same formula as you have shown above.

  13. Using below table and knowing User ID and a Date that would fall somewhere between the Valid From and Valid Until looking to identify the Role at a given point in time.
    For example, for jsmith1 on 1/6/2022, want it to return Tech Agent

    Valid From Valid Until User ID Name Name Last/First Role Site
    11/4/2021 12/1/2021 jsmith1 John Smith Smith, John Agent Wichita
    12/1/2021 12/19/2021 jsmith1 John Smith Smith, John Agent Wichita
    12/19/2021 1/1/2022 jsmith1 John Smith Smith, John Agent Wichita
    1/1/2022 1/9/2022 jsmith1 John Smith Smith, John Tech Agent Wichita
    1/9/2022 1/16/2022 jsmith1 John Smith Smith, John Special Agent Wichita
    1/16/2022 1/30/2022 jsmith1 John Smith Smith, John Manager Wichita
    1/30/2022 2/1/2022 jsmith1 John Smith Smith, John Manager Wichita
    2/1/2022 2/6/2022 jsmith1 John Smith Smith, John Supervisor Wichita

    • Hi!
      Have you tried the ways described in this blog post? Check the formula below, it should work for you:

      =INDEX(D1:D10,MATCH(1,(A1:A10<$K$1)*(B1:B10>$K$1)*(C1:C10="jsmith1"),0))

  14. Hello.

    I have 2 sheets. The first sheets has part numbers, Qty produced and Material Name, Material Needed(has formula) The second sheet has the part numbers and qty produced( exported from our system) I wanted to vlookup the Qty Produced on sheet 2 to sheet1 so I could compute my material requirement. Some of the values displays but most displays "N/A". I have already changed the format for both sheets to text or numbers so they would have the same but the N/A still appears. The part numbers which is the lookup value are numeric and alpha numeric. Thank you and I hope you can help me with my problem.

    • Hi!
      Unfortunately, without seeing your data, it is difficult to give you any advice. N/A error means no value was found. There may be extra spaces or other characters.

  15. Worked perfect. Thanks!

  16. Sorry formula is:

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

    • Sorry again, but the post is not showing "does not equal" to Beer.

  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?

  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. Hey there, i have problem, my boss ask me to compile big data where i have 2 column and 1 column have multiple criteria, he want me only show result which is not 0.
    example:
    A | B | C |
    -----------------------------------------
    Anna | Sales 1 | 1 |
    -----------------------------------------
    Anna | Cashier | 36 |
    ----------------------------------------
    Anna | Sales 2 | 0 |
    ----------------------------------------
    Sally | Sales 1 | 0 |
    ----------------------------------------
    Sally | Cashier | 75 |
    ----------------------------------------
    Sally | Sales 2 | 2 |
    ----------------------------------------

    just imagine i have big data more than the 2 criteria in coloumn B, and my boss ask me to take only Sally in sales area which not 0 ( cause in column C, only have 0 and other number but have more than 1 sales type) so the result should be

    A | B |
    -----------------------
    Sally | 2 |
    -----------------------
    Anna | 1 |

  20. I want to know how to compare 2 column such as:
    A B
    ABC | GOOD
    ABC | GOOD
    ABC | GOOD = TRUE

    A B
    ABC | GOOD
    ABC | GOOD
    ABC | BAD = FALSE

    Is it possible?

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