How VLOOKUP can return multiple results

**BOB202** · 11-09-2012, 12:22 PM

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

**jake.masters** · 11-09-2012, 12:31 PM

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.

**Underling** · 10-29-2013, 07:50 PM

Originally Posted by jake.masters

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!

**Tony Valko** · 10-29-2013, 08:01 PM

I suggest that you start your own thread.

Make sure you include the details.

**azumi** · 10-29-2013, 11:01 PM

Yes it's possible retrieve multiple results

Azumi

**FDibbins** · 10-29-2013, 11:03 PM

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.

**Tony Valko** · 10-30-2013, 09:57 AM

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.

**Tony Valko** · 10-30-2013, 07:48 PM

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?

**Underling** · 11-07-2013, 03:50 PM

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