+ Reply to Thread
Results 1 to 3 of 3

Lookup & return Multiple Results

  1. #1
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,090

    Lookup & return Multiple Results

    awhile back, I started compiling some tricks I learned. So this would probably come in handy here! Here's one that lots of people are asking in this forum. I picked up the original formula here in http://www.get-digital-help.com/2009...okup-in-excel/ & tweaked it a little to be able to exclude errors & copied over to other columns & rows.

    Basically, to use this formula to your own scenario, change C$6:$C$12 to the range of the multiple results you want to show. And all $B$6:$B$12 to the lookup range. After that, press CTRL + SHIFT + ENTER to confirm the formula.
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    MS 2007, 2010
    Posts
    12,425

    Re: Lookup & return Multiple Results

    One of my favorites. I would suggest for Excel 2003 and below

    =IF(COUNTIF($B$6:$B$12,$E6)<COLUMN(A1),"",INDEX($C$6:$C$12,SMALL(IF($E6=$B$6:$B$12,ROW($B$6:$B$12)-MIN(ROW($B$6:$B$12))+1,""),COLUMN(A1))))
    entered as an array

    and for 2007 and up
    =IFERROR(INDEX($C$6:$C$12,SMALL(IF($E6=$B$6:$B$12,ROW($B$6:$B$12)-MIN(ROW($B$6:$B$12))+1,""),COLUMN(A1))),"") also as an array
    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 Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,090

    Re: Lookup & return Multiple Results

    using the COUNTIF at the start is pretty cool. shall use that next time if someone asks. thanks, ChemistB!

+ 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