Vlookup or Index for multiple results two different sheets

[kac1125]

Hello,

Looking do do a vlookup to pull back multiple records for the same lookup value. for example in sheet1 starting A1

Item	Vendor
1234	11237

<tbody>
</tbody>

I want to lookup all values that start with 11237 in sheet2 starting A1

Vendor ship

11237-00001

11237-00003

11237-00002

18716-00001

18213-00004

19173-00001

<tbody>
</tbody>

So I want it to pull back

Item	Vendor	Vendor ship
1234	11237	11237-00001
		11237-00003
		11237-00002

<tbody>
</tbody>

Any help would be much appreciated.

 

[Special-K99]

Try (untested)

=IFERROR(INDEX(Sheet2!A$1:A$1000,AGGREGATE(15,6,ROW(A$1:A$1000)/(VLOOKUP(1234,Sheet1!A$1:B$1000,2,0)=(LEFT(Sheet2!A$1:A$1000,5)+0)),ROWS(A$1:A1))-(1-1),COLUMN()-(COLUMN()-1),1),"")

or this (array formula)
=IFERROR(INDEX(Sheet1!$A$1:$A$1000,SMALL(IF((VLOOKUP(1234,Sheet1!A$1:B$1000,2,0)=(LEFT(Sheet2!A$1:A$1000,5)+0)),ROW($A$1:$A$1000)),ROW(A1))-(ROW(A$1)-1),COLUMN()-(COLUMN()-1),1,"")

Not sure if the LEFT() will work on a range though

 

[AhoyNC]

Same idea as post by Special-K99, but a little shorter.
Formula in C2 is an array formula that must be entered with CTRL-SHIFT-ENTER.
If you have Excel ver. 2010 or later you could the formula in D2.

Both of these formulas assume the Vender Number in cell B1 is numeric and not text.
If it's text change the formulas to:

=IFERROR(INDEX(Sheet2!$A$2:$A$7,SMALL(IF(LEFT(Sheet2!$A$2:$A$7,LEN($B$2))=Sheet1!$B$2,ROW(Sheet2!$A$2:$A$7)-ROW(Sheet2!$A$2)+1),ROWS($C$2:C2))),"")

<colgroup><col></colgroup>

or

=IFERROR(INDEX(Sheet2!$A$2:$A$7,AGGREGATE(15,6,(ROW(Sheet2!$A$2:$A$7)-ROW(Sheet2!$A$2)+1)/(LEFT(Sheet2!$A$2:$A$7,LEN($B$2))=$B$2),ROWS($D$2:D2))),"") Excel Workbook A B C D 1 Item Vendor Vendor ship Vendor ship 2 1234 11237 11237-00001 11237-00001 3 11237-00003 11237-00003 4 11237-00002 11237-00002 Sheet1 Excel Workbook A 1 Vendor ship 2 11237-00001 3 11237-00003 4 11237-00002 5 18716-00001 6 18213-00004 7 19173-00001 Sheet2 <strike></strike>

<colgroup><col></colgroup>

<strike></strike>

 

[kac1125]

Thank you very much! I really appreciate your help on this!