+ Reply to Thread
Results 1 to 2 of 2

Index Match based on multiple Match criteria that returns each subsequent occurence.

  1. #1
    Registered User
    Join Date
    07-05-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2011
    Posts
    2

    Index Match based on multiple Match criteria that returns each subsequent occurence.

    I am trying to write an Index Match formula that will return each subsequent occurrence of the Match criteria. The Match criteria is 2 part, contains 2 different variables to match.

    I have a formula to return the 1st occurrence of matching the 2 separate criteria. It is,
    {=MATCH(1,($Q$20=$F$2:$F$500)*(AG$1=$B$2:$B$500),0)}

    When I put that part in to the following formula it returns the first occurrence of the matched criteria,
    {=IF(ISNA(INDEX($B$2:$D$500,MATCH(1,($F3=$B$2:$B$500)*(G$1=$A$2:$A$500),0),3)),"",INDEX($B$2:$D$500,MATCH(1,($F3=$B$2:$B$500)*(G$1=$A$2:$A$500),0),3))}

    In order to return subsequent values I believe I should use a formula like the following containing the Small function using the Row function in tandem,
    {=INDEX($C$3:$C$7,SMALL(IF(($B$10=$B$3:$B$7),MATCH(ROW($B$3:$B$7),ROW($B$3:$B$7)),""),ROWS($A$1:A1)))}

    How would I combine these 2 ideas to achieve my goal?

    The attached workbook contains the data set and the desired outcome. The cells highlighted in yellow are the subsequent values that I need the formula to return. The second formula is what I am using in the workbook, the third formula is just an example of a working formula of the type I think I need to use.

    Bonus, my results need to appear horizontally instead of vertically. I believe there is a way to do that by using Column instead of Row in the last part of the third formula.

    Thanks,
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,410

    Re: Index Match based on multiple Match criteria that returns each subsequent occurence.

    How about in J3:
    Please Login or Register  to view this content.
    Drag down and accross
    Quang PT

+ 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: 1
    Last Post: 01-18-2019, 03:55 PM
  2. How to sum Index/Match based on multiple criteria ?
    By donny007 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-18-2019, 02:06 AM
  3. [SOLVED] Index-Match based on multiple criteria
    By JBR9999 in forum Excel General
    Replies: 2
    Last Post: 09-01-2016, 02:14 PM
  4. [SOLVED] Index/Match based on multiple criteria
    By BB1972 in forum Excel General
    Replies: 7
    Last Post: 08-17-2012, 11:45 AM
  5. Multiple value occurence index/match
    By mechen8 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-01-2012, 07:01 AM
  6. Replies: 3
    Last Post: 01-10-2011, 06:14 PM
  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