+ Reply to Thread
Results 1 to 6 of 6

Index return multiple matches

  1. #1
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Azerbaijan
    MS-Off Ver
    Excel 2016
    Posts
    145

    Index return multiple matches

    Hello Dear community,


    I use following formula to return name of product based on values. But I think I am doing something wrong, because it is returning same names if values are same.


    Can anyone tell me , what I am doing wrong please?

    Thanks
    Attached Images Attached Images

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

    Re: Index return multiple matches

    Your formula similar to a simple lookup value in column K in range H:I and returns coresponding name in column I
    With "return name of product based on values", what exactly are you looking for?
    Extract unique name , sorting on volume Z-A?
    PLs try to attach a sample worksheet. (See instruction on yellow banner on top of the page)
    Quang PT

  3. #3
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Azerbaijan
    MS-Off Ver
    Excel 2016
    Posts
    145

    Re: Index return multiple matches

    I just need to return all matches based on column K to column L

    for example K7 is 845, so I need to lookup 845 in column H ( which is row 18 ) and return I18 , but there can be another 845, so I want formula to find next match

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

    Re: Index return multiple matches

    With INDEX(SMALL...) Its array formula, need to confirm by Ctrl-Shift-Enter

    Alternative by using AGGREGATE:

    =INDEX($I:$I,AGGREGATE(15,6,ROW($H$1:$H$40)/($H$1:$H$40=K1),COUNTIF($K$1:K1,K1)))

  5. #5
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Azerbaijan
    MS-Off Ver
    Excel 2016
    Posts
    145

    Re: Index return multiple matches

    Thank you very much, can I know what is difference between this formula and array formula?

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

    Re: Index return multiple matches

    AGGREGATE(15,6,...) skip the errors from number/0
    SMALL(IF(... establish an array, that require Ctrl-Shift-Enter.
    Both help to get small value.

+ 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. INDEX MATCH MATCH and return MAX value of multiple matches
    By njm0059 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-24-2018, 05:29 PM
  2. [SOLVED] Using Index/Small to return multiple, non-duplicated matches
    By Brawnystaff in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-08-2017, 03:42 PM
  3. [SOLVED] Return multiple matches using index function
    By sbabu16 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-27-2015, 05:01 AM
  4. [SOLVED] INDEX MATCH array formula that matches substring n gives multiple matches
    By bkwins in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-12-2013, 04:57 AM
  5. return multiple matches using INDEX and SMALL
    By merlyn45 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-31-2013, 05:14 AM
  6. Replies: 2
    Last Post: 08-16-2012, 09:00 AM
  7. Replies: 4
    Last Post: 07-18-2012, 02:34 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