+ Reply to Thread
Results 1 to 9 of 9

How VLOOKUP can return multiple results

  1. #1
    Registered User
    Join Date
    01-31-2012
    Location
    manhattan
    MS-Off Ver
    Excel 2007
    Posts
    6

    How VLOOKUP can return multiple results

    when i make a vlookup it return one result what is the first

    how can i make to return multiple results

    for example: a price sheet that have 2 similar items and i want to lookup by item name it should bring in both items

    Thanks in Advanced

  2. #2
    Forum Contributor
    Join Date
    09-13-2012
    Location
    Atlanta, USA
    MS-Off Ver
    Office 2003, 2010
    Posts
    230

    Re: How VLOOKUP can return multiple results

    You will probably need to use Match and Index rather than VLOOKUP, which will always just return the first value. The only way around this is to set a dynamic range, which is more difficult.

    http://www.mrexcel.com/articles/exce...ndex-match.php

    After you match the item, simply add a +1 to the index of the second item and it will pull in the information one cell down.


    Match looks like: =MATCH(Value, array, < > or =)

    Index Looks like: =INDEX(Array, Row #,[column #])

    To use them together, nest match inside of index as the row #.

    =INDEX(A1:A30, MATCH(part number, table of part numbers, 0))

    For the next part number, if it is right below it,

    =INDEX(A1:A30, MATCH(part number, table of part numbers, 0)+1)

    If the second part # is not one cell below it, this will be more complicated, and you should post a sample of your issue.

  3. #3
    Registered User
    Join Date
    10-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: How VLOOKUP can return multiple results

    Quote Originally Posted by jake.masters View Post
    You will probably need to use Match and Index rather than VLOOKUP, which will always just return the first value. The only way around this is to set a dynamic range, which is more difficult.


    If the second part # is not one cell below it, this will be more complicated, and you should post a sample of your issue.


    I am trying to deal with a spreadsheet in which the items are not directly below eachother. If this channel is dead can someone help me transfer this over to a new forum to ask this question? Thanks!

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How VLOOKUP can return multiple results

    I suggest that you start your own thread.

    Make sure you include the details.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: How VLOOKUP can return multiple results

    Yes it's possible retrieve multiple results

    Azumi

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How VLOOKUP can return multiple results

    Underling, welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How VLOOKUP can return multiple results

    Ok, why is Underling's post breaking the rules but this post is not?

    Read the thread starting at this post:

    http://www.excelforum.com/excel-form...ml#post3456566

    You guys (mods/admins) need to get together and clarify this rule so we're (responders/question askers) not confused as to what the policy (rule) is.

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How VLOOKUP can return multiple results

    Ok, I just figured out a way to avoid the confusion of this issue...

    I'll just not read or respond to "old" threads.

    Man, how easy was that?

  9. #9
    Registered User
    Join Date
    10-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: How VLOOKUP can return multiple results

    Thanks and sorry guys! I thought it might be against the rules but it was just so relevant that it was driving me crazy! I was trying to get ahold of the guy who answered the question to see if you could expand on the other guys question (because my question was exactly the same as his!! =) I started a new post and referred to this one so they could get the background information relatively quickly. Sorry Tony Valko =0

+ 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