Vlookup or Index for multiple results two different sheets

kac1125

New Member
Joined
Jul 31, 2014
Messages
44
Hello,

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

<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
ItemVendorVendor ship
12341123711237-00001
11237-00003
11237-00002

<tbody>
</tbody>

Any help would be much appreciated.
 

Some videos you may like

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
Joined
Nov 7, 2006
Messages
8,350
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
Joined
Oct 10, 2011
Messages
4,613
Office Version
  1. 365
Platform
  1. Windows
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,109,020
Messages
5,526,296
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top