+ Reply to Thread
Results 1 to 8 of 8

MATCH 3 criteria in a table with INDEX & MATCH

  1. #1
    Registered User
    Join Date
    04-11-2014
    Location
    Iowa
    MS-Off Ver
    Office 2013
    Posts
    20

    MATCH 3 criteria in a table with INDEX & MATCH

    Could someone tell me why this isn't working? Just trying to INDEX & MATCH three criteria, which are one right after the other in a table, column B>index (what I want) columns C,D, & E have the criteria needed to filter to the correct item number.

    {=INDEX('ITEM# Matrix'!B1:E2988,MATCH(O5,'ITEM# Matrix'!C1:C2988,0),MATCH(P1,'ITEM# Matrix'!D1:D2988,0),MATCH(V1,'ITEM# Matrix'!E1:E2988))}

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,367

    Re: MATCH 3 criteria in a table with INDEX & MATCH

    INDEX and MATCH with multiple criteria:

    =INDEX(results_ranges,MATCH(1,(range_crit1=crit1)*(range_crit2=crit2)*(range_crit3=crit3),0))

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: MATCH 3 criteria in a table with INDEX & MATCH

    What type of data is the formula supposed to return? Text? Numeric? Could be either? Something else?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    04-11-2014
    Location
    Iowa
    MS-Off Ver
    Office 2013
    Posts
    20

    Re: MATCH 3 criteria in a table with INDEX & MATCH

    I actually figured it out myself, about 5 min after I posted the question. I saw another post where they used "&" in the formula. Works like a charm. Now if I could get my other question answered (Sum, Index, Match and put into ascending order).

    =FERROR(INDEX($B$2:$B$2988,INDEX(MATCH($O4&$P$1&$V$1,$C$2:$C$2988&$D$2:$D$2988&$E$2:$E$29880,0),0)),"")

    Thanks for your help azumi & Tony V. I appreciate it.

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: MATCH 3 criteria in a table with INDEX & MATCH

    Glad you got it working.

    Just for the heck of it here's a more efficient way to write that formula.

    Array entered**:

    =IFFERROR(INDEX($B$2:$B$2988,MATCH($O4,IF($D$2:$D$2988=$P$1,IF($E$2:$E$29880=$V$1,$C$2:$C$2988)),0)),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  6. #6
    Registered User
    Join Date
    04-11-2014
    Location
    Iowa
    MS-Off Ver
    Office 2013
    Posts
    20

    Re: MATCH 3 criteria in a table with INDEX & MATCH

    Thanks Tony.

    Was wondering if you had any insight on my other post here?
    "Sum, index, match two columns and put into ascending order"

    I can't seem to get any bites, from anyone?
    Thanks for the assist.

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: MATCH 3 criteria in a table with INDEX & MATCH

    Sounds familiar.

    Post a link to that thread.

  8. #8
    Registered User
    Join Date
    04-11-2014
    Location
    Iowa
    MS-Off Ver
    Office 2013
    Posts
    20

    Re: MATCH 3 criteria in a table with INDEX & MATCH


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  2. [SOLVED] VLOOKUP or INDEX/MATCH when referencing a table to determine two criteria are being met
    By gunnerau in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-27-2014, 07:01 AM
  3. Index and Match with Two Criteria, and date Match Type is Less Than
    By ExcelQuestion in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 11-11-2013, 08:57 AM
  4. Index Match based on 2 Criteria only returning 1 match
    By stsanders22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-04-2012, 11:26 PM
  5. [SOLVED] Need help using Index and Match for table lookup with 2 criteria matching
    By lorne17 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-27-2012, 09:26 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1