vlookup (or other function) to pull multiple matches in the look up table

[Jeffrey Green]

In column A I have a distinct part number.
In two other columns, i have that same list of parts, but it is not unique in the lookup table. each instance will have a different value in the 2nd column.

One part in Column A may exist 3 times (or more) in the look up table.
In column B, i want the first match of the the part number with the value in the 2nd column. i can do this with Vlookup.
But in Column C, i want the 2nd instance of my part in column A . . . i want the 2nd value from the lookup table.
iIn Column D, i want the third instance of that part (assuming there is a third)

Etc.

I can add as many columns as i need to pull in all instances.

thanks, i hope i explained that clearly

 

[Jeffrey Green]

I knew this would happen . . . . . Although i would like to know the solution above, what i decided to do the old STACK 'Em up method. Add another column next to my Unique list, then STACK my two column lookup table underneath, then Sort by part number . . . .

thanks . . i am all set, unless you want to tell me how to do this like i asked orginally . . .

 

[AhoyNC]

Something like this?
Copy formula down and across as needed.

Excel Workbook
	A	B	C	D	E	F
1	Part
2	Part1	330	516	164
3	Part2	674	774	167	409
4	Part3	254
5	Part4	416	651
6
7	Look UP Table
8	Part1	330
9	Part2	674
10	Part1	516
11	Part1	164
12	Part2	774
13	Part3	254
14	Part2	167
15	Part2	409
16	Part4	416
17	Part4	651
Sheet