+ Reply to Thread
Results 1 to 5 of 5

INDEX/MATCH more than 1 result available but only displaying the 1st result

  1. #1
    Registered User
    Join Date
    10-11-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    53

    INDEX/MATCH more than 1 result available but only displaying the 1st result

    Hi,

    I have a main data table and this is used to pull the relevant information, I have the majority of this working just one small issue.

    The check table will frequently have the same KWO more than once as the order is a two part check i.e Rack & Cable

    With my standard go to INDEX/MATCH formula it gives me the first result every time. (as expected)
    However I need the formula to provide the unique type identifiers.

    I have tried various formulas but none are what I need most just give me a list of types based on the first KWO it finds.
    These tables will constantly be evolving and more data added to them so I need it to be flexible to a large degree.

    P.S attached file is a stripped down version due to work confidentiality.

    Thanks
    Mark
    Attached Files Attached Files

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,644

    Re: INDEX/MATCH more than 1 result available but only displaying the 1st result

    g6=IFERROR(INDEX(tbl_Release[Order Type Server/Rack/Cable],SMALL(INDEX((tbl_Release[KWO No]<>[@[KWO No]])*10^10+ROW(tbl_Release[KWO No])-ROW(tbl_Release[[#Headers],[KWO No]]),0),COUNTIF(F$6:F6,F6))),"")
    Please Login or Register  to view this content.
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,631

    Re: INDEX/MATCH more than 1 result available but only displaying the 1st result

    something similar (copied down):

    =IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(tbl_Release[KWO No])/(tbl_Release[KWO No]=[@[KWO No]]),COUNTIF(F$6:F6,F6))),"")
    Attached Files Attached Files
    Glenn



  4. #4
    Registered User
    Join Date
    10-11-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    53

    Re: INDEX/MATCH more than 1 result available but only displaying the 1st result

    Thanks for the swift replies.

    I have opted to use Glenn's formula as it suits my needs better.
    nflsales your formula works perfect on the sample data but when added to my main file its randomly picks and chooses which cells to populate with data in the order type column

  5. #5
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,631

    Re: INDEX/MATCH more than 1 result available but only displaying the 1st result

    You're welcome.

+ 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 , 0 instead of N/A when no result
    By muss1210 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-22-2017, 01:02 PM
  2. [SOLVED] INDEX/MATCH generates incorrect result by referencng nearest cell if match not found
    By aglawrence in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-29-2017, 08:59 AM
  3. [SOLVED] Index/Match - More than 1 Result
    By LJenny in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 10-13-2015, 11:08 AM
  4. [SOLVED] Index/Match result should again search for zero result
    By ursanil in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2014, 04:45 AM
  5. Wrong result displaying using IFERROR-INDEX-MATCH-ROW Formula
    By paradise2sr in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-16-2013, 01:03 PM
  6. [SOLVED] Index Match if result is not something
    By Jaron_t in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-19-2012, 01:19 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