How to use VLOOKUP to return multiple values

Acholate · Aug 6, 2013

Hi, I am new to forums

I know this already been asked but I still newbie and can't understand how to modify it to my situation.

I look into these thread but only understand some of the concept.

http://www.mrexcel.com/forum/excel-questions/231882-vlookup-return-all-matches.html
http://www.mrexcel.com/forum/excel-questions/395345-lookup-returns-multiple-values.html
http://www.mrexcel.com/forum/excel-questions/362743-vlookup-duplicates.html

Here is my sample, please take a look.
LookupSample.xlsx

Currently in sheet SOs column B, I using vlookup, which obviously does not work.

I want to return multiple values not just the first one that found.
For example, B3 should return SO560700167 :06/07/2013 instead of SO560700288 :10/07/2013

If someone would provide me an exact formula, I would be very appreciated.

Acholate · Aug 7, 2013

someone help me out please!

Acholate · Aug 7, 2013

AhoyNC · Aug 7, 2013

Maybe these videos will help.

 Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row w Formula - YouTube
Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table w Formula - YouTube

Acholate · Aug 8, 2013

AhoyNC said:
Maybe these videos will help.

 Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row w Formula - YouTube
Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table w Formula - YouTube

Thanks for these videos.
I spending a hour try to apply formula to my case but still does not work.
Something went wrong in my formula, I feeling so stupid right now

This sound a bit selfish request, but I think I need someone to provide me an exact formula.

Acholate · Aug 8, 2013

L. Howard · Aug 9, 2013

Hi Acholate,

As far as I know, your formula will not work with all the duplicates in column A of sheet SOLIST.

If you delete the entry in A1 of that list you do get the return you want, because it was a duplicate of A2 and the formula was using the first of two identical values to do the lookup.

Secondly, the Lookup_value is not typically referred directly to an entry in the first column of the lookup_array, but rather to a cell reference or in the formula itself.

So:

Range F1 = Apple
F1 can also be a drop down with many fruit names, selecting any fruit then becomes the lookup value to the second formula.
With the first formula, you would have to 'hand enter' another fruit name to do a different lookup.

=VLOOKUP("Apple",A1:B25,2,0)
=VLOOKUP(F1,A1:B25,2,0)

Both formulas would return the same value from column B.

As would this on your sheet SOLIST.
Where F1 has a drop down and is a list of A1,A2,A3,A4...etc.

In F1 you select A2 and it is displayed in F1.

=OFFSET(A2,0,6)
=OFFSET(INDIRECT(F1),0,6)

Returns the value you were seeking in your post but I don't think it will suit you overall intentions you have with your workbook.

Most likely you will need to rearrange the data, but I don't know exactly what you are trying to do with your data.

Here is the proper syntax for vlookup. For whatever that is worth.
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Regards,
Howard

Aladin Akyurek · Aug 9, 2013

Acholate said:
Hi, I am new to forums

I know this already been asked but I still newbie and can't understand how to modify it to my situation.

I look into these thread but only understand some of the concept.

http://www.mrexcel.com/forum/excel-questions/231882-vlookup-return-all-matches.html
http://www.mrexcel.com/forum/excel-questions/395345-lookup-returns-multiple-values.html
http://www.mrexcel.com/forum/excel-questions/362743-vlookup-duplicates.html

Here is my sample, please take a look.
LookupSample.xlsx

Currently in sheet SOs column B, I using vlookup, which obviously does not work.

I want to return multiple values not just the first one that found.
For example, B3 should return SO560700167 :06/07/2013 instead of SO560700288 :10/07/2013

If someone would provide me an exact formula, I would be very appreciated.

<tbody>
</tbody>

When you prepare a file for asking questions, you should also the desired results, manually calculated.

The sheet SO's lists every look up value a number of times. This is in fact not correct as one normally not know how many times a value would occur. Instead try the following:

SOs...

B2, control+shift+enter, not just enter, and copy across:

Rich (BB code):

=IFERROR(INDEX(Solist!$G$1:$G$29,
  SMALL(IF(ISNUMBER(SEARCH("|"&$A2,"|"&Solist!$A$1:$A$29)),
  ROW(Solist!$G$1:$G$29)-ROW(Solist!$G$1)+1),COLUMNS($B2:B2))),"")

Acholate · Aug 9, 2013

L. Howard said:
Hi Acholate,

As far as I know, your formula will not work with all the duplicates in column A of sheet SOLIST.

If you delete the entry in A1 of that list you do get the return you want, because it was a duplicate of A2 and the formula was using the first of two identical values to do the lookup.

Secondly, the Lookup_value is not typically referred directly to an entry in the first column of the lookup_array, but rather to a cell reference or in the formula itself.

So:

Range F1 = Apple
F1 can also be a drop down with many fruit names, selecting any fruit then becomes the lookup value to the second formula.
With the first formula, you would have to 'hand enter' another fruit name to do a different lookup.

=VLOOKUP("Apple",A1:B25,2,0)
=VLOOKUP(F1,A1:B25,2,0)

Both formulas would return the same value from column B.

As would this on your sheet SOLIST.
Where F1 has a drop down and is a list of A1,A2,A3,A4...etc.

In F1 you select A2 and it is displayed in F1.

=OFFSET(A2,0,6)
=OFFSET(INDIRECT(F1),0,6)

Returns the value you were seeking in your post but I don't think it will suit you overall intentions you have with your workbook.

Most likely you will need to rearrange the data, but I don't know exactly what you are trying to do with your data.

Here is the proper syntax for vlookup. For whatever that is worth.
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Regards,
Howard

Thank you very much for your reply.
I cant believe that I mess up vlookup formula, my bad for not checking properly
but I do understand vlookup concept.
Here is my fix: LookupSample2.xlsx
This time is what I talking about in first post.
I want to return multiple values not just the first one that found.

For example, B3 should return SO560700167 :06/07/2013 instead of SO560700288 :10/07/2013
B5 should return SO560700305 :11/07/2013 not duplicate same value again from B4 SO560700631 : 29/07/2013

Sorry again, for making you all the way to explain vlookup concept to me

Aladin Akyurek · Aug 9, 2013

Acholate said:
...
I want to return multiple values not just the first one that found.

See my reply...

How to use VLOOKUP to return multiple values

Acholate

New Member

Excel Facts

Acholate

New Member

Acholate

New Member

AhoyNC

Well-known Member

Acholate

New Member

Acholate

New Member

L. Howard

Well-known Member

Aladin Akyurek

MrExcel MVP

Acholate

New Member

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

How to use VLOOKUP to return multiple values

New Member

Excel Facts

New Member

New Member

Well-known Member

New Member

New Member

Well-known Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock