Check Multiple Criteria with Excel INDEX and MATCH

The INDEX function can return a value from a range of cells, and the MATCH function can calculate a value's position in a range of cells.

For example, in the screen shot below, cell A7 contains the item name, Sweater:

  • the MATCH function can find "Sweater" in the range B2:B4. The result is 1, because "Sweater" is in the first row of that range.
  • the INDEX function can tell you that in the range C2:C4, the first row contains the value 10.

So, by combining INDEX and MATCH, you can find the row with "Sweater" and return the price from that row.

=INDEX($C$2:$C$4,MATCH(A7,$B$2:$B$4,0))

indexmatchprice01

Find a Match for Multiple Criteria

In the previous example, the match was based solely on the Item name – Sweater. Sometimes life, and Excel workbooks, are more complicated.

In the screen shot below, each item is listed 3 times in the pricing lookup table. To get the right price, you'll need to specify both the item name and the size. We want to find the price for a large jacket.

indexmatchmulticriteria00

Does it MATCH? True or False

Instead of a simple MATCH formula, we'll use one that checks both the Item and Size columns. To do something similar on a worksheet, we could add columns to check the item and size columns.

  • If the Item in column B is a Jacket, the result in column E is TRUE. If not, the result is FALSE
  • If the Size in column C is Large, the result in column F is TRUE. If not, the result is FALSE

In column G, when you multiply the TRUE/FALSE values, the result is 1, only if both are TRUE.

indexmatchmulticriteria03

We could use a MATCH formula to find the position of a 1 in column G, in the screen shot above. The 8th row of data (worksheet row 9), has the 1, and that row will give us the correct price for a large jacket.

Use MATCH With Multiple Criteria

Instead of adding extra columns to the worksheet, we can use an array-entered formula to do all the work. Here is the formula that we'll use to get the correct price, and below is the explanation:

=INDEX($D$2:$D$10,
MATCH(1,(A13=$B$2:$B$10)*(B13=$C$2:$C$10),0))

NOTE: This is an array-entered formula, so press Ctrl + Shift + Enter, instead of just pressing the Enter key.

In this example,

  • prices are in cells D2:D10, so that is the range that the INDEX function will use.
  • item name is in cell A13
  • size is in cell B13.

The formula checks for the selected items in $B$2:$B$10, and sizes in $C$2:$C$10. The results are multiplied.

  • (A13=$B$2:$B$10)*(B13=$C$2:$C$10)

The MATCH function looks for the 1 in the array of results.

  • MATCH(1,(A13=$B$2:$B$10)*(B13=$C$2:$C$10),0)

If you select that part of the formula and press the F9 key, you can see the calculated results. In the screen shot below there are 9 results, and all are zero, except the 8th result, which is 1.

indexmatchmulticriteria04

So, the INDEX function returns the price – 40 – from the 8th data row in column D (cell D9).

indexmatchmulticriteria01

To find the product code for the selected item and size, you would change the formula to look in cells A2:A10, instead of the price column.

=INDEX($A$2:$A$10,
MATCH(1,(A13=$B$2:$B$10)*(B13=$C$2:$C$10),0))

In this example, the product code would be JK003, from cell A9.

More INDEX and MATCH Examples

For more tips and examples, and to download the sample file, please visit the INDEX and MATCH functions page on the Contextures website. This is Example 4 in the sample file.

_____________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

76 comments to Check Multiple Criteria with Excel INDEX and MATCH

  • Check Multiple Criteria with Excel INDEX and MATCH « Contextures … | Excel Needs

    [...] the original: Check Multiple Criteria with Excel INDEX and MATCH « Contextures … ← Learningcomputer.com: FREE IT Training @ [...]

    • David

      I am trying to do the same thing in my workbook as you have done here. I have tried several different formulas and get inconsistent results. I used your formula and changed the ranges to match mine

      =INDEX(Formulas!$F$2:$F$100,)MATCH(1,($D12=Formulas!$D$2:$D$100)*(E12=$E$2:$E$100),0))

      and I get an N/A error. I have entered with Ctrl+Shift+Enter. Though I want to take it one step further. I want to take the formula one step further and add a quantity multiplier to the formula. =(INDEX(Formulas!$F$2:$F$100,)MATCH(1,($D12=Formulas!$D$2:$D$100)*(E12=$E$2:$E$100),0)*B12) In several versions of the formula it works but in some multiplying by a quantity changes the row that it returns the results from. Any thoughts as to why it isn't working in my workbook?

      • Christiaan Lübbe

        Hi David
        I had the same problem using this formula - getting the N/A error. I've gone back and forth via Google, and finally got my problem sorted. Try using Ctrl+Shift+Enter when you enter the formula into Excel. This then recognises the formula as an array (putting {} around the formula - do not do this manually, it doesn't work!). Hope that sorts your problem out?

  • Lynda

    I don't remember if I first learned about this from you or from Mike Alexander's BaconBits blog or from Mr.Excel.com, but it is **SO** worth learning to use! Seriously life-changing - lol! (yeah, my co-workers look at me like I'm weird or something...)

  • Mark

    I use a slightly different technique using the SUMPRODUCT function combined with the ROW function inside the INDEX, then I don't have to remember to make it an array formula. Your formula in the example, "=INDEX($A$2:$A$10,
    MATCH(1,(A13=$B$2:$B$10)*(B13=$C$2:$C$10),0))" would be written as "=INDEX($A$2:$A$10,
    SUMPRODUCT(($B$2:$B$10=A13)*($C$2:$C$10=B13),ROW($A$2:$A$10)))". The assumption is that your multiple criteria will yield a distinct result.

  • Elias

    @Mark, SUMPRODUCT doesn’t work if there is more than one record that matches your search criteria.

    If the Ctrl+Shift+Enter is an issue, we can modify Debra’s formula to avoid it.

    =INDEX($A$2:$A$10,MATCH(1,INDEX((A13=$B$2:$B$10)*(B13=$C$2:$C$10),0),0))

    Regards

    • Ryan

      In cases where there is more than one record that matches the search criteria, can the formula be modified to find the 2nd or 3rd record that matches?

  • Jeff Weir

    Excel's database function DGET would be perfect for this (not that there's anything wrong at all with your great Index/Match example). Alas, for once you haven't posted your data...so I haven't prepared an example...*sigh*

  • Maxime Manuel

    It sounds like a VLOOKUP to me when we combine theose INDEX and MATCH. Am I right?

  • govind

    The data is as under

    emp.no. gc date1 gc1 date1 gc2 date2
    22101 4 1-1-99 9 1-1-04 11 1-1-07
    2839 9 1-1-95 11 1-1-00 14 1-1-04

    empno gc2 date2
    2839 14 ??

    what formula i can put at question marks so as to arrive at the date viz. 1-1-2004

    regards govind

  • Rajan

    Dear Sir,

    I am using a MS excel formula as below ;

    =INDEX(table1,MATCH($B9,INDEX(table1,,1),0),MATCH(AJ$5,INDEX(table1,1,),0))

    I get answer in return as per the search by this formula.

    But in the case where there is 0 (zero) in the answer searched by this formula I want blank
    (i.e. " ") instead of 0 zero)

    So what formula should I use.

    Please suggest.

    Thank you.

    Rajan.

  • Jeff Weir

    Rajan...this is best handled by using a custom number format that hides zeros. For instance, apply the following custom format to the results cells:
    #,##0; #,##0;
    …or this for dollars:
    $#,##0; $#,##0

    But if your lookup table only contains strings – and not numbers - you can also do it by amending your formula by adding an empty string on to the end using this:
    &""
    ...which turns any numbers to text. i.e. like this:
    =INDEX(table1,MATCH($B9,INDEX(table1,,1),0),MATCH(AJ$5,INDEX(table1,1,),0)),0,"")& ""

  • SHASHI

    i have two tables
    what i need is to retrieve the matching credit loan number with the following criteria....where credit date should be greater then the debit date....and the corresponding amount should be retrieved....
    table 1
    LOAN DEBIT DATE AMOUNT
    A 1-Apr-2012 10000
    B 1-May-2012 30000
    C 1-Jun-2012 50000
    D 1-Jul-2012 2000
    E 1-Mar-2012 40000
    F 1-May-2012 80000
    G 1-Oct-2012 15000

    table 2
    LOAN CREDIT DATE AMOUNT
    B 1-Mar-2012 40000
    C 1-May-2012 80000
    D 1-Oct-2012 15000
    B 1-Jul-2012 25000
    A 1-May-2012 2000
    R 1-Jun-2012 40000
    S 1-Jul-2012 80000

  • zeddy

    i am not an excel expert just have basic ideas.i need your help as i need to calculate the below mentioned question...i dnt know how please help.
    q:

    One time payment plan(MIS) payment plan(FD)
    Months Months
    Rank Designation 66 120 36 60 84 120 & above

    1 Marketing Member 6% 10% 7% 8% 9% 12%
    2 Sr.Marketing Member 1.80% 1.80% 2.00% 2.00% 2.00% 2.00%
    3 Sales Executive 1.00% 1.00% 1.30% 1.30% 1.30% 1.30% 1.30%
    4 Sr.Sales Executive 0.90% 0.90% 1.20% 1.20% 1.20% 1.20%
    5 Sales Manager 0.80% 0.80% 1.00% 1.00% 1.00% 1.00% 1.00%
    6 Sr.Sales Manager 0.60% 0.60% 0.80% 0.80% 0.80% 0.80%
    7 Sales Inspector 0.60% 0.60% 0.80% 0.80% 0.80% 0.80% 0.80%
    8 Sr.Sales Inspector 0.60% 0.60% 0.80% 0.80% 0.80% 0.80%
    9 Development Officer 0.60% 0.60% 0.80% 0.80% 0.80% 0.80%
    10 Sr.Development Officer 0.60% 0.60% 0.80% 0.80% 0.80% 0.80%
    11 Development Manger "0.50%" 0.50% 0.70% 0.70% 0.70% 0.70%
    12 Sr.Development Manager 0.50% 0.50% 0.70% 0.70% 0.70% 0.70%
    13Regional Marketing Officer 0.50% 0.50% 0.70% 0.70% 0.70% 0.70%
    14Regional Marketing Manager 0.50% 0.50% 0.70% 0.70% 0.70% 0.70%
    15Chief Marketing Manager 0.50% 0.50% 0.70% 0.70% 0.70% 0.70%

    Question is that if the rank is Development Manager and the Product is MIS and Term is 66 months then what would be the income of the person on the basis of the declared percentage(under " ").Now I need a way that If i put only the rank,term and amount then the earning should come automatically.Please help...

  • Adil Modak

    Hi, how can we we get multiple column headers returned in one row.
    i.e:
    17 18 19 20 21 22
    Yes Yes
    Yes No
    if 19 and 21 row contains Yes, then which formula can return Column Name(i.e 19,21) in 22.

    Please help, highly appreciated.

  • Rakesh

    i have 3 column like A, B, C and now i want to formula for A-B=C but in this formula if data is available then by calculation is working fine. but if in some case B Column data is blank then what formula i have to use.

    Please help!!!!!

  • Tek

    This is a great how and why. I am however getting a #value error because I'm using text, but I'm not sure how to fix that situation as the compare should be returning a number value. essentially I havethe following: index(column A, match(1(column b)*(column c),0)). all columns are text. If I understood correctly I was returning a 1=true,0=false value so I don't understand the #value error I'm getting. any guidance is appreciated.

  • Abid Farooq

    I have use this formula to look up value from form but in cell show error #Value!
    =INDEX(Timein,)*MATCH(1,(A7=IDList)*(A11=Date),0)

    Pleas help me in this problem

  • Jonathan

    Suuuuuuuuper helpful post, Debra, thanks so much!! Very nicely displayed and very clear. Keep up the good work!

  • Machtyn

    Thank you for this tutorial. It has been very helpful.

    I do note one problem with it. I'm usually working with data in a variable set of rows or rows that might have blank cells between them. As such, this formula seems to be failing for me.

    Using your example, I tried something similar to:
    =INDEX($D:$D,MATCH(1,(A13=$B:$B)*(B13=$C:$C),0))
    but the result was #NUM. I then tried:
    =INDEX($D:$D,MATCH(1,(A13=$B$2:$B$11)*(B13=$C$2:$C$11),0))
    and the return value was 30, the value in the cell just above the desired result. EDIT: I see the mistake in the second code, I forgot to put $D$2:$D$11. It actually does work when I fix that. However, it is curious to me that I may be hitting an Excel array limitation when trying to use full column matching.

    • Machtyn

      A13 and B13 in my example is where I put my values for Sweater and Large.

      When I went back to the attempt on my spreadsheet, which was 6201 rows of data, attempted to do the F9 trick on my inner match function, I got a "Formula is too long." popup error from Excel.

    • Machtyn

      OK, final reply. I figured out what my problem was. There is no array limitation that I was running into. I was confusing a mathematical formula with a string manipulation formula. Still, the $A:$A thing wasn't working with the example. Now, onto my problem for which there will have to be different solution. (I'll probably use VBA in a macro.)

      When I was first using Match, it was using the first value as my search term. That is, I had a column of data with [nameOfGroup: groupID] and my reference value was only the groupID. So, my Match function looked like (where column A held my indeterminant number of rows of data to be searched and H2 held the groupID being searched):
      match("*"&H2,$A$2:$A$6202,0)

      I modified it, using the example above, to be:
      match(1,("*"&H2=$A$2:$A$6202),0)

      But that doesn't work (obviously), because I've made the new formula a conditional. And the conditional doesn't work the way I intended it.

      Ahh.... now to go stretch my coding brain again.

      • Machtyn

        sorry, final, final post.
        My solution, a bit klunky, but it works. Essentially, I use FIND. (SEARCH works, too).

        =INDEX($D$2:$D$6202,MATCH(1,FIND(H3,$A$2:$A$6202)/FIND(H3,$A$2:$A$6202))*(I3=$B$2:$B$6202),0))

        What is happening is I use the FIND to return the number the value is found within the search term and then divide it by itself, which gives me a 1.

        Astute readers will see the problem: "What happens if a match isn't found?" It will return a divide by 0 error (0/0). Excel handles this situation by putting a #N/A on the line. It shouldn't matter. You will get the result you want and be able to handle the results not found situations.

  • I call your page as "EXCEL HEAVEN"
    Thanks for the learning! Keep Sharing! :D

  • Ross

    Hi, your posts on index match saved me! One strange issue arises, however, with a three-criteria index match. The first two criteria are a date and a ticker symbol. The third criteria is a text string written as "Buy". When I use the evaluate formula tool, it matches both the date and symbol as a string (ie. "41200Aud.Usd"), then attaches "Buy" at the end, gives the coordinates in the table referenced, then returns a value. In the table, however, no "41200Aud.UsdBuy" exists! The formula seems to be making it up! There is a "41200Aud.UsdCover", but with the match formula set to 0, this value should be ignored.

    Here is the formula:
    {=IFERROR(INDEX('AC - Financials.xlsm'!AF_I_Ledger[#All],MATCH(1,('AC - Financials.xlsm'!AF_I_Ledger[[#All],[Date]]=$B8)*('AC - Financials.xlsm'!AF_I_Ledger[[#All],[Ticker]]=$C$3)*('AC - Financials.xlsm'!AF_I_Ledger[Transaction]="Buy"),0),6),"")}

    Dates are in column B, symbol cell C3.

    The 'AC - Financials.xlsm'AF_I_Ledger[#All]' is a data table in another workbook. The formula fails to apply the exact match condition to the third criteria "('AC - Financials.xlsm'!AF_I_Ledger[Transaction]="Buy")"

    If you could tell me where went wrong it would be wonderful!
    Thank you!
    Ross

  • Lopes

    TextBox3.Value = (TextBox2.Value * WorksheetFunction.Index(Worksheets("TABELAS").Range("C4:ALO40"), WorksheetFunction.Match(TextBox2.Value, Worksheets("TABELAS").Range("B4:B40"), -1), WorksheetFunction.Match(TextBox1.Value, Worksheets("TABELAS").Range("C3:ALO3"), 0), 1))

    or

    Label6 = "=(TextBox2*INDEX(TABELAS!R4C3:R40C1003,MATCH(TextBox2,TABELAS!R4C2:R40C2,-1),MATCH(TextBox1,TABELAS!R3C3:R3C1003,0),1))"

    please helpme

  • Allie

    So not an excel pro! Im trying to understand this for matching purposes... my use for this would be to compare 2 worksheets to find matching items. is there a way for the worksheet to highlight matches found- or something to identify that they match? I work in accounts payable and the way of printing and checking off is so old fashion and time consuming! anything to help me here?
    to try to explain it better...

    i have our info and our contractors invoice - i need to compare our PO# and price with their invoice (which also has a PO# and price) but i need to make sure i identify it correctly and mark off the match so i can see it....

    Help make this quicker for me!!! thanks!

  • I want to do this except Sweater appears on one tab, and sweater and price appear on another worksheet. On worksheet #2 sweater is not the first field (so VLOOKUP won't work). Thoughts?

    Worksheet #1
    Column A-D have stuff with Column E the word "Sweater"

    Worksheet #2
    Columns A,B,C contain stuff, Column D contains "Sweater", and column E "Price"

    Further complicating matters "Sweater" and "Price" could be in any columns on any one of a dozen worksheets. Any way to search for something across worksheets and return a value from the row where it's found?

  • karthik

    Pls help me wit this

    i want a formula to match different criteria(=,=) in different sheet has a reference

  • Ashraf

    Hi
    =INDEX(L$42:L$107,MATCH(1,($K$42:$K$107=$J8)*($J$42:$J$107=$I8),0))
    how to avoid pressing ctrl+shift+enter
    -i am using excel with solidworks , and i use something they call it configure publisher

    regards
    Ashraf

  • BHAVYA

    hI,

    =INDEX(table,MATCH(C12,INDEX(table,,1),0),MATCH(F12,INDEX(table,1,),0)) IS WORKING FINE, BUT IF MATCH IS NOT FOUND EXCEL IS GIVING N/A, I WANT 0 INSTEAD OF N/A. COULD ANY BODY COMMENT ON THIS

  • There is an alternate solution to this, and that's using an Ampersand within the MATCH formula.

    {=INDEX($D$2:$D$10,MATCH(A13&B13,$B$2:$B$10&$C$2:$C$10))}

    You still must CTRL+SHIFT+ENTER to make it an array formula, however this entirely eliminates the need to do math and match to a 1. This instead will simply look over both columns at the same time, and both must be true.

  • zql

    This is exactly what I've been looking for. Thank you so much!

  • ramlal meena

    Empty cell in Excel file has changed in the month, while 0 is 1 reply should have come to help please

  • Apostolos

    Dear excel experts, can you offer any help on which function or combination of functions to use in order to solve the following issue:

    I have an excel table (with many columns) that "result" in 4 final columns [columns: Z, AA, AB, AC]
    Each cell in each of these columns contains a function. The specific combination of functions used in cell Z2 is: =IF(ISBLANK(M2),"--",IF(D2=M2,1,IF(D2M2,"X")))
    The result is either -- or 1 or X
    At the top row of these columns [in cells: Z1, AA1, AB1, AC1] there is a person's name.
    Up to this point all is as it should be.
    I now wish, and seek your help for, to be able to do the following:
    To input a function (or a combination of functions?) in cell AD2 which will result in the correct person's name (located in cells Z1, AA1, AB1, AC1) when the value of 1, appears only in one of the 4 cells [cells Z2 through AC2]. Below is the example of my problem:

    Columns: Z AA AB AC AD
    Row 1 Jane Nick John Dick
    Row 2 1 1 1 X
    Row 3 1 X 1 X
    Row 4 X 1 X X Nick
    Row 5 X 1 1 X
    Row 6 X X 1 X John
    etc.

    Can anyone help with suggesting the appropriate function combination for column AD?
    I thank you very very much for any tip that anyone can provide (I hope not VBA)!
    Apostolos

  • srinivas

    please help me for stocks location
    i have same product in different location, qty
    exp: Producta - 100 nos - Loc 12A
    Producta - 100 nos - Loc 13A

    Iam issuing stocks from loc 12A for 100 and when i check for next issue the location how can i retrive

  • Sankar

    It is helpful.
    but You are returning a value based on and conditions.
    but how to return a value based on OR condition .

    DB:
    Sankar Y
    Senthil Y
    Sankar Y
    Vinod N
    Gokul Y
    Senthil N

    Should return a Values which name is either (Senthil and Y)--If anyone Condition Satisfies means it has to return

  • Joaquim

    Hello,

    I have trouble using the INDEX & MATCH with multiple criteria.
    I have multiple Excel files containing a lot of data that looks like this:

    MSC/NASTRAN
    END LOADS

    ELEM GID1 GID2 TYP 3000000 3002100 3002104 3002111 3002205 3002219

    0 1070017 9185117 P 74.1 2.2 82.8 74.2 21.3 57.8
    0 1070017 9185118 P 98.4 82.4 10.8 0.3 21.4 72.5
    0 1070018 9185114 P 74.6 43.1 18 86.4 19 32.2
    0 1070018 9185115 P 22.5 41.6 74 98.1 58.9 28.9
    0 1070019 9185113 P 87.5 14.9 63.6 92.7 26.2 37.9
    0 1070020 9185112 P 34 11.1 83 51.6 66.5 59.1
    0 1070067 9185116 P 99.9 76 61.1 23.5 2.8 79.7
    0 1070517 9185617 P 35.3 82.4 63.4 7.5 36.8 55.5
    0 1070517 9185618 P 11.6 70.7 89.7 17.3 95.4 29.5
    0 1070518 9185614 P 16.7 66 87.1 31.4 10 48.5
    0 1070518 9185615 P 49.5 72.9 62.5 68.3 93.6 51
    0 1070519 9185613 P 18.4 0.9 51.6 20.3 72.2 6.5

    Please note that this file contains thousands of rows and hundreds of columns...

    In a separate file that I call EXTRACTOR, I want to extract data from the previous file.

    There are 4 criteria that I want to check: ELEM, GID1, GID2 AND TYP

    In the EXTRACTOR, the user has to specify the name of the file from where he wants to extract data. He is also asked to give then name of the sheet (please note that the file containing the data has to be open)

    Name of file containing all loads (with correct extension): loads.xlsx
    Name of sheet containing all loads: sheet1

    Then I do a CONCATENATE of these 2 entries in cell N6 (in EXTRACTOR) in order to have: [loads.xlsx]sheet1!
    This will be used further in cell E12 formula...

    Now, in EXTRACTOR, I type (starting at cell A11):

    ELEM GID1 GID2 TYP 3000000 3002100 3002104 3002111 3002205 3002219
    0 1070017 9185117 P #N/A

    Cell E12 displays "#N/A" and contains the following big formula:

    = INDEX(
    CONCATENATE($N$6, ADDRESS(6, COLUMN(E10),2),":", ADDRESS(15000, COLUMN(E10),2) ),
    MATCH(1,
    ($A12= CONCATENATE($N$6,"$A$6:$A$15000") )
    *
    ($B12= CONCATENATE($N$6,"$B$6:$B$15000") )
    *
    ($C12= CONCATENATE($N$6,"$C$6:$C$15000") )
    *
    ($D12= CONCATENATE($N$6,"$D$6:$D$15000") ),0),1
    )

    This formula doesn't seem to work properly...
    When I do EVALUATE FORMULA, I get FALSE in every MATCH criteria check, which in the end gives me #N/A within the MATCH, and also #N/A for the global formula

    I hope my description of the problem is clear enough.
    If somebody can help, it would be very very appreciated!

    By the way, using MS Excel 2010 on XP 64 bits

    Thank you!

    J.

  • Sobuj

    Hi, This is a very helpful tutorial. I was wondering that, how I can use Match function to find a value in a range of cells between certain values such as,
    =INDEX($A$4:$A$12,MATCH(1,(G13>=$E$4:$E$12)*(G13<=$F$4:$F$12),0)). Thanks in advance

  • Josh

    I've used the index/match array version to lookup multi-critreria data and it works great! However, I'm experiencing a problem when sorting on columns outside the column that contains the array formula. It seems as if the relative cell references do not follow the sort. Can anyone help or explain why this may happen?

    Thanks!

  • Jeff Weir

    @Josh: Can you post a sample workbook somewhere, or explain a little bit more fully what your data looks like and what you are trying to do?

  • Jeff Weir

    @Joaquim: This is a little hard to conceptualize. Can you post a sample workbook somewhere and post the link here?

    • Joaquim

      @Jeff: Thank you for your reply! I will provide such a sample at the beginning of next week. Thanks again :-)
      P.S.: Any suggestion where to put the sample?

  • Jeff Weir

    @Sankar: Your question doesn't make sense to me...can you clarify further? You say you want to use an OR condition, but your example " return a Values which name is either (Senthil and Y)" is a single AND condition wrapped in an OR condition.

  • Jeff Weir

    @Sobuj: That formula works fine, PROVIDED you array enter it. i.e. push CTRL + SHIFT + ENTER to enter the formula. It will then appear in the formula bar with curly brackets surrounding it:
    {=INDEX($A$4:$A$12,MATCH(1,(G13>=$E$4:$E$12)*(G13<=$F$4:$F$12),0))}

  • Jeff Weir

    @Joaquim: Save it somewhere like Google Docs or SkyDrive or DropBox; make the file public, and post a link here.

    • Joaquim

      @Jeff: What I'm trying to do is work related and since I can't use Google Docs, SkyDrive or DropBox at work, I think it would be easier to send you the file directly to your inbox. Is that ok with you?

  • Joaquim

    @Jeff: What I'm trying to do is work related and since I can't use Google Docs, SkyDrive or DropBox at work, I think it would be easier to send you the file directly to your inbox. Is that ok with you?

  • Jeff Weir

    @Joaquim...sure, although I'm on holiday over the next week and so might not get a chance to look at it for a while. weir dot jeff at gmail dot com.

  • MSJ

    So interesting hybrid!
    I tried to use that multi-criteria 'Match+Index' command in a macro while refering to arrays built in the same macro, but I received "Type mismatch" error!
    How can I use it in a VBA routines not using any worksheet.range/cell address?

  • [...] Check Multiple Criteria with Excel INDEX and MATCH | Contextures Blog "Use MATCH With Multiple Criteria" section [...]

  • AG

    Hi Debra
    I'd like to use the match function (or any other you suggest better) to filter a table under one criteria and display the filtered records in a drop-down list.
    For example, I have a list of electrical devices that operate at different voltages and I want to display in a drop-down list only those devices that operate at 277 volts.
    How to configure the dropdown to achieve this task?
    Thanks much!
    AG

  • AG: This might help - http://www.contextures.com/xlDataVal15.html
    Or this one - http://www.contextures.com/xlDataVal02.html

    Personally I prefer the first approach, although you might find that the 2nd is simpler.

  • Hello
    Ive successfuly used Index and Match but I have a problem

    For example the data is
    green apple | wallmart | 5
    green apple | wallmart | 23
    red apple | wallmart | 5
    orange | macy's | 4

    I want to get all green apple's sold in wallmart, and get the amount of each for example
    5,23,5

    when using index match, i can only get the first one it matches to.. how to get multiple values and concatenate them like this?

  • Thanks! I've been trying to figure this out all day! It works on my worksheet! WooHoo!!

  • Sean Mulcahy

    Liked A. Roberts ampersand solution and having done it with the earlier one, I cannot get the ampersand to work. This is even when copying and pasting his formula?

  • I am trying to use and Index & Match function where the matches are coming from a Data Validation List and the indexing criteria & matches are in another tab. It appears to me I've written the formula ok, but am still getting the #N/A result, which I'm guessing is because of the data validation list. Any thoughts? Below is the formula I've written.

    {=INDEX('All Data'!$K:$K,MATCH($C$19&$E$19&$H$19,'All Data'!$A:$A&'All Data'!$C:$C&'All Data'!$E:$E,0),1)}

    E19 & H19 are the match criteria from the data validation list. All three criteria need to be met in order to return the correct value in All Data column K.

    I appreciate any help you can give me.

  • vicktor schausberger

    Hi. I am trying to compare two array and find duplicates, I already check for index, match do not work, and(exact do not work, conditional formatting countif do not work, let me give you a little example.
    13 16 17 40 42 44 10 11 12 17 28 46
    1 12 22 44 46 52 3 9 11 21 24 49
    10 13 35 36 46 42 11 24 36 45 46 47
    9 26 34 40 42 49 3 6 36 46 48 52
    1 2 25 43 48 53 2 7 11 15 43 45
    8 32 35 46 47 52 3 6 16 30 31 40
    20 44 46 48 52 53 20 44 46 48 52 53
    15 17 22 40 41 45 29 33 46 48 49 53

    • @victor you can use SUM and COUNTIF in an array-entered formula.

      For example, if the first sets of numbers are in B2:G2, and I2:N2, put this formula in cell P2:

      =SUM(COUNTIF(B2:G2,I2:N2))

      Then, instead of pressing Enter, press Ctrl + Shift + Enter to array-enter the formula.
      Copy the formula down to row 7.

  • vicktor schausberger

    sorry is two arrays 6by6 the first six are 13.16.17.40.42.44 compare against 10.11.12.17.28.46 and so on thanks.

  • vicktor schausberger

    Thanks for your time, I write exactly what you say, but pop a message formula have error.

  • the formula you type have an error, pop the same messages. thanks. anyway I got a vba code that resolve my problem.
    again thank you so much for reading my post and tried to help. keep in touch.

  • Hi,

    SIR I HAVE A SHEET IN WHICH I HAVE 6 COLUMNS FOR RICE INVENTORY.

    1)Report No
    2)IN/OUT/PROCESS/Fg(This column show whether Rice In, out,Proceeds & Finshed goods)
    3)Product Name
    4) In (If In & FG i have make entry of quantity in this column)
    5) Out (If out or process i have make entry of quanitity in this column)
    6) Balance

    Now i want that balance column check the report number, product name , & whether it is IN OUT Or other so it give me balance

    If it is IN or FG so it add or if it out or Process its less it.

  • Azhar CM

    Hai,

    Please tell me how I can retrieve data from below two worksheets

    sheet 1:

    Date Sales
    15/10/2013 Honda
    20/10/2013 Benz
    23/10/2013 Toyota

    Sheet 2:
    Date Sales

    if I enter the date on the second sheet, how I can retrieve sales data using formula

    Hoping to hearing from you soon

    Thanks

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>