+ Reply to Thread
Results 1 to 3 of 3

INDEX(MATCH based on two criteria

Hybrid View

  1. #1
    Registered User
    Join Date
    09-11-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    53

    INDEX(MATCH based on two criteria

    I need to use an INDEX(MATCH formula based on two criteria. The attached spreadsheet works using 1 criteria, I just need to now base it on 2.

    =IFERROR(INDEX('All Transactions'!$B$2:$B$10,MATCH(1,INDEX(('All Transactions'!$A$2:$A$10=$B$5....  second criteria here?.....)*(COUNTIF(C$9:C10,'All Transactions'!$B$2:$B$10)=0),),0)),"")
    See attached for better explanation. Any help would be greatly appreciated
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,373

    Re: INDEX(MATCH based on two criteria

    try

    =IFERROR(INDEX('All Transactions'!$B$2:$B$10,MATCH(1,INDEX(('All Transactions'!$A$2:$A$10=$B$5)*('All Transactions'!$D$2:$D$10=$C$4)*(COUNTIF(C$9:C9,'All Transactions'!$B$2:$B$10)=0),),0)),"")

  3. #3
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365 Version 2404
    Posts
    1,259

    Re: INDEX(MATCH based on two criteria

    Another option:

    Array enter (ctrl+shift+enter) this and copy across and down.

    =IFERROR(INDEX('All Transactions'!$B$2:$B$10,SMALL(IF('All Transactions'!$A$2:$A$10=$B$5,IF('All Transactions'!$D$2:$D$10=C$4,ROW('All Transactions'!$A$2:$A$10)-1)),ROW($N1))),"")

+ 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. [SOLVED] Index/Match when the Match criteria is based on a second table
    By Plummet in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-10-2017, 01:02 PM
  2. VBA Code for Index/Match/Match to input into excel based on 2 criteria inside form
    By Carl Fisher in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-22-2017, 12:11 PM
  3. Index & Match Based on Criteria
    By cychua in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-03-2013, 06:03 AM
  4. Index-Match based on 3 criteria
    By Johnelbek in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-16-2013, 08:10 AM
  5. 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
  6. Index / Match / Large based on a criteria.
    By SimpleJack in forum Excel General
    Replies: 7
    Last Post: 06-20-2012, 08:04 AM
  7. Index and Match Based on Multiple Criteria
    By duranbeaz in forum Excel General
    Replies: 2
    Last Post: 05-22-2009, 04:37 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