How to use VLOOKUP to return multiple values

[Acholate]

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>

 

[Acholate]

bump

 

[Aladin Akyurek]

Yes Exactly



Unfortunately no, there are other entries as well.
I guest I need to show you the full data of my work, please take a look.

stock forecast.xlsx

All that bumping didn't help me to understand what you are after. That stock file contains many sheets with the same content.

You have in SO's the following, shown partially:

F1A-A01-DUSS

F1A-A01-DUSS

F1A-A03-DUSS

F1A-A03-DUSS

F1A-A04-DUSS

<tbody>
</tbody>

You want to look up these values in Solist. Yet you seem to know exactly that the item F1A-A01-DUSS occurs twice in Solist. I noted this strangeness already a few items. Apparently, this does not disturb you. Newbie or not, this is strange.

Taking up the list above as given...

SOs

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

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

 

[Acholate]

All that bumping didn't help me to understand what you are after. That stock file contains many sheets with the same content.

You have in SO's the following, shown partially:

F1A-A01-DUSS

F1A-A01-DUSS

F1A-A03-DUSS

F1A-A03-DUSS

F1A-A04-DUSS

<tbody>
</tbody>

You want to look up these values in Solist. Yet you seem to know exactly that the item F1A-A01-DUSS occurs twice in Solist. I noted this strangeness already a few items. Apparently, this does not disturb you. Newbie or not, this is strange.

Taking up the list above as given...

SOs

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

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

That formula is what I looking for! Thank you so so much, you are lifesaver!

I don't know beforehand that F1A-A01-DUSS occurs twice in Solist. The Solist's data is updated by import company's database report into excel. This report contain information about sales order which are not yet deliver. I create these Sos and Solist sheet in order to check which product, which SO are not yet deliver. I hope this answer your question, it a bit hard for me to explain cause my english is bad.

Anyway, thank you again, I will treat you a meal if you come to Thailand!

 

[Aladin Akyurek]

That formula is what I looking for! Thank you so so much, you are lifesaver!

I don't know beforehand that F1A-A01-DUSS occurs twice in Solist. The Solist's data is updated by import company's database report into excel. This report contain information about sales order which are not yet deliver. I create these Sos and Solist sheet in order to check which product, which SO are not yet deliver. I hope this answer your question, it a bit hard for me to explain cause my english is bad.

Anyway, thank you again, I will treat you a meal if you come to Thailand!

Ok, you are welcome. Thanks for providing feedback and for the meal.