Returning multiple values with VLOOKUP?

Originally Posted by Krussadams

Hi all,

Daily, I work with a spreadsheet which lists items that need to be purchased to satisfy manufacturing demand (electronic components). The problem I am having arises because our internal part numbers (which are the only part number displayed on my report) are entirely different than the manufacturer's part numbers.
Right now, I have a separate spreadsheet that lists all internal part numbers and their corresponding manufacturer part numbers (Master Parts List). I use VLOOKUP to retrieve the manufactuer's part number for each internal number, but because of the limitations of VLOOKUP, I can only retrieve the first manufacturer's part number listed. For the majority of internal part numbers, there are 2 or more (up to 8) manufacturer's part numbers approved/associated. Does anyone know a way where I can do a VLOOKUP (or something similar) and have it retrieve ALL associated manufacturer part numbers?
To clarify, the sheet I have as a reference (Master Parts List) lists our internal part number on a row (in column A), then lists the manufacturer's part number on the same row (in column E). When multiple manufacturer part numbers are associated with the same internal part number, the row is duplicated and the internal part number remains the same, but the manufacturer's part number is different.

Here's a visual simulation if it helps:

Internal P/N | Mfg P/N
-------------------------------
XX123456 | ABC123
XX123456 | DEFGHI
XX123456 | 456789
XX123457 | J1K2L3
XX123458 | MNOPQ
XX123458 | R4S5T6

If this needs further clarification or explanation, please let me know. I will not be surprised.


Thanks in advance!!!

-Keith

ASSUME the following conditions:

A1:A200 -- contain the Internal P/N
E1:E200 -- contain the Mfr's P/N

Cell F1 is where you will enter the Internal P/N so you can get the corresponding Mfr's P/N.

Try this ... in Cell F2, enter this formula:

=index($E$1:$E$200,small(if($A$1:$A$200=F$1,row($A$1:$A$200)-1,""),
row($A$1:$A$200)-1))

BTW, the above formula should be commited using the Ctrl-Shift-Enter keys and not just the Enter key. Copy this formula down to suit your requirements.

Hope this helps you.

Regards.