+ Reply to Thread
Results 1 to 3 of 3

Issue: Only returning 1st match on Index/Match

  1. #1
    Registered User
    Join Date
    11-19-2014
    Location
    Montgomery, PA
    MS-Off Ver
    2013
    Posts
    1

    Issue: Only returning 1st match on Index/Match

    Hi Guys,

    I have a bunch of unique identification numbers and the ID numbers cannot be used more than once.

    ID number eligibility is determined by the index/match function (4 criteria). (formula works up to this point)
    {=INDEX(Table!$A$4:$AT$80,MATCH(1,(Table!$K$4:$K$80=K11)*(Table!$AT$4:$AT$80=M11)*(Table!$N$4:$N$80=O11)*(Table!$AQ$4:$AQ$80='1st result fail'!B11),0),1)}

    Where the formula falls apart is that it will only return the first ID number that meets the criteria. I need it to return a unique ID number that meets the index/match function criteria each time.

    Any ideas?

    Sincerely,
    Taylor
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Issue: Only returning 1st match on Index/Match

    So on your formula sheet, you have this criteria
    ABC KS 08 Yellow multiple times. Do you want it to bring back a different ID each time?
    What if the # of times it's on formula sheet is greater than the # of times it's in Table?
    For example, you have those criteria three times in 1st result fail and only 2 times in Table. What does it return the third time?
    What if the number of times it's on formula sheet is less than the # of times its in Table?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

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

    Re: Issue: Only returning 1st match on Index/Match

    Maybe

    Put in A6 and press CTRL-SHIFT-ENTER button together and copied down and cross :

    =INDEX(Table!$A$4:$A$80,SMALL(IF((Table!$K$4:$K$80=$K6)*(Table!$N$4:$N$80=$O6)*(Table!$AQ$4:$AQ$80=$B6)*(Table!$AT$4:$AT$80=$M6),ROW(Table!$A$4:$A$80)-ROW(Table!$A$4)+1),COUNTIFS($B$6:B6,B6,$K$6:K6,K6,$M$6:M6,M6,$O$6:O6,O6)))

    Cheers
    Attached Files Attached Files

+ 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] Match Issue - Consolidate Columns of Data With Match/Index/etc?
    By excelsior123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-03-2014, 05:14 PM
  2. Issue using Index/Match to pull multiple occurrences of "Match" criteria
    By aridfriedman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-09-2014, 11:38 AM
  3. 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
  4. [SOLVED] index match match - issue
    By jw01 in forum Excel General
    Replies: 2
    Last Post: 11-27-2012, 03:53 PM
  5. [SOLVED] Index & Match returning incorrect value. Arrays fixed and exact match used.
    By SDes in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-07-2012, 08:29 PM

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