+ Reply to Thread
Results 1 to 5 of 5

Complex index/match lookup

  1. #1
    Registered User
    Join Date
    06-10-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2019 for Mac
    Posts
    26

    Complex index/match lookup

    I've looked everywhere for a solution to this but I can't find one and anything I think of seems not to work properly or is rather inelegant. I have 2 columns that look like this:

    James Apples
    James Bananas
    John Apples
    John Pears
    Jack Bananas

    I'm trying to find a formula that returns the fruit for a specified name so that I can drag the formula down and have them listed. Like this: (for James)

    Apples
    Bananas

    So far I've managed to get it to work by using a combination of INDEX(), MATCH(), COUNTIF(), etc. but I run into a problem where the number of different fruits for a name exceed the number of rows until the first fruit for that specified name. It's difficult to explain. This is the formula I have so far.

    Please Login or Register  to view this content.
    I've attached an illustration that might be clearer. Will someone please say they can think of an elegant solution to this?

    problem.xlsx

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Complex index/match lookup

    If I understand you correctly:

    This formula in A3 and down: =IF(B3=$A$21,ROW(),"")
    This formula in B21 and down: =IF(COUNTIF($B$3:$B$7,$A$21)>ROW(A1)-1,VLOOKUP(SMALL($A$3:$A$7,ROW(A1)),$A$3:$C$7,3,0),"")

  3. #3
    Registered User
    Join Date
    06-10-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2019 for Mac
    Posts
    26

    Re: Complex index/match lookup

    Gee, that was quick. That looks like it works. Thanks a lot.

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Complex index/match lookup

    You're welcome. Don't forget to mark your thread as SOLVED (instructions are in rule #9 which can be found by clicking Forum Rules @ top of page).

  5. #5
    Registered User
    Join Date
    06-10-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2019 for Mac
    Posts
    26

    Re: Complex index/match lookup

    Right, of course.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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