vlookup, returning the 2nd match,3rd match...

[jmitchells5w]

I have a large data set of part numbers and descriptions (10,000 rows, 10 columns). This is not a unique list - there may be duplicates part numbers (up to 4) in column A. I am creating a unique part number list on a seperate work sheet and am using countif to determine how many times a part number is listed in column A of my data set. I can use the vlookup formula to find the first found match (col A) and return the value from column B. I found the formula =LOOKUP(2,1/(A1:A10000=N4),B1:B10000) to find the last found match, but is there a way to find the other matches? (2nd match, 3rd match??). My second sheet is laid out such that:

Column A: Unique Part Numbers
Column B: CountIf to determine how many times the part number appears in col A of my data set.
Column C: vlookup formulula to find first found match, returns value from col B of data set
Column D: ?? (need to find 2nd found match if any), returns value from col B of data set
Column E: ?? (need to find 3rd found match if any), returns value from col B of data set
Column F: lookup formula to find last found match, returns value from col B of data set

I'm still reading posts to find an answer but so far this morning I have not found anything. any help would be greatly appreciated!!

Jmitchell

 

[barry houdini]

You coud use this formula in D2

=IF(B2>=2,INDEX(data!B$1:B$10000,SMALL(IF(data!A$1:A$10000=A2,ROW(data!A$1:A$10000)-ROW(data!A$1)+1),2)),"")

This is an array formula that needs to be confirmed with CTRL+SHIFT+ENTER

replace the red 2s with 3s for the third instance......