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[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Vendor[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]11237[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I want to lookup all values that start with 11237 in sheet2 starting A1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Vendor ship[/TD]
[/TR]
[TR]
[TD]11237-00001
[/TD]
[/TR]
[TR]
[TD]11237-00003
[/TD]
[/TR]
[TR]
[TD]11237-00002[/TD]
[/TR]
[TR]
[TD]18716-00001
[/TD]
[/TR]
[TR]
[TD]18213-00004[/TD]
[/TR]
[TR]
[TD]19173-00001[/TD]
[/TR]
</tbody>[/TABLE]

So I want it to pull back
[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Vendor[/TD]
[TD]Vendor ship[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]11237[/TD]
[TD]11237-00001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]11237-00003[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]11237-00002[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 1285"]
<colgroup><col></colgroup>[TR]
[TD]=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))),"")[/TD]
[/TR]
[/TABLE]
or
[TABLE="width: 1285"]
<colgroup><col></colgroup>[TR]
[TD]=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>
[/TD]
[/TR]
[/TABLE]
<strike></strike>

 

[kac1125]

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