Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Vlookup To Return Multiple Values

  1. #1
    Join Date
    14th April 2007
    Posts
    17

    Vlookup To Return Multiple Values

    Is there a way where i can vlookup a column and return all matches if there are multiple values?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Six Strings Guest

    Re: Vlookup To Return Multiple Values

    There was a thread on this just within the last couple of days or so.

    Please read the responses from Shg in this thread.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: Vlookup To Return Multiple Values

    Please read the responses from Shg in this thread.
    Thanks for the plug, Six Strings. In that post, I provided a formula for looking up catenated cells in catenated columns -- not what the OP is looking for here, by my reading.

    I think the OP here is looking to return the 1st, 2nd, 3rd ... match of a VLOOKUP (or the equivalent). I expect it could be cobbled together by using INDEX and MATCH, using some kind of catenation with COUNTIF. I tried briefly, but couldn't finish it. A helper column would certainly work.

    adnanriaz, can you post an example?
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  4. #4
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,295

    Re: Vlookup To Return Multiple Values

    Assuming that A2:B10 contains the lookup table, D2 contains the lookup value, and the corresponding values from Column B are to be returned, try the following...

    E2:

    =COUNTIF(A2:A10,D2)

    F2, copied down:

    =IF(ROWS(F$2:F2)<=$E$2,INDEX($A$2:$B$10,SMALL(IF($A$2:$A$10=$D$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(F$2:F2)),2),"")

    ...confirmed with CONTROL+SHIFT+ENTER. Note that the number 2 at the end of the formula determines the column from which to return the corresponding values. In this case, the corresponding values are to be returned from the second column or Column B. See the attached file.

    Hope this helps!
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    14th January 2011
    Posts
    5

    Re: Vlookup To Return Multiple Values

    I found this and it works great for what I am trying to do. However, I found that if my table information is on another sheet (which is where I need it to be) I can't get it to work. Any ideas?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Vlookup To Return Multiple Values

    tig08ger,

    Please do not post your question in threads started by others - - this is known as thread hijacking.
    Always start a new thread for *YOUR* question. If you find it helpful to clarify your needs you can include a link to this ( or any other ) thread.

    When starting a new thread be sure to give it a search-friendly title that aptly describes your need.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Can Vlookup return multiple values?
    By Maxi in forum EXCEL HELP
    Replies: 20
    Last Post: 3 Weeks Ago, 19:36
  2. Vlookup & Return All Found Values
    By sayed_harris in forum EXCEL HELP
    Replies: 5
    Last Post: September 15th, 2010, 03:38
  3. vlookup & return all values
    By lollipop in forum EXCEL HELP
    Replies: 8
    Last Post: September 2nd, 2010, 04:08
  4. Replies: 5
    Last Post: April 2nd, 2008, 19:18
  5. Vlookup Return Same Values With Text File
    By kaide in forum EXCEL HELP
    Replies: 11
    Last Post: October 3rd, 2006, 19:57

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
  •  
porno