# Vlookup or Index for multiple results two different sheets

#### kac1125

##### New Member
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.

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Special-K99

##### Well-known Member
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

##### Well-known Member
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
ABCD
1ItemVendorVendor shipVendor ship
212341123711237-0000111237-00001
311237-0000311237-00003
411237-0000211237-00002
Sheet1
Excel Workbook
A
1Vendor ship
211237-00001
311237-00003
411237-00002
518716-00001
618213-00004
719173-00001
Sheet2

<strike></strike>

<colgroup><col></colgroup>
<strike></strike>

#### kac1125

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

Replies
11
Views
581
Replies
2
Views
102
Replies
3
Views
337
Replies
4
Views
257
Replies
1
Views
176