vlookup hitting duplicate values?

[LucidCheetah]

I built a spreadsheet for tracking containers at work a while back. it would search column b of sheet2 for the value input into sheet 1 and return the associated value found in column a on sheets 2. Initially we did not have any duplicate container IDs. Having added new containers to our inventory we now have some containers with overlapping ID#s. Problem now is that the vlookup seems to stop at the first instance of the number it finds and returns the associated value from column a. If the Container in questions is further down the list, we get the wrong informationr eturned associated with the first value. is there a way using vlookup that it can identify the duplicate value?

 

[Aladin Akyurek]

Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet2!$A$2:$A$200,SMALL(IF(Sheet2!$B2:$B$200=$A2,ROW(Sheet2!$A$2:$A$200)-ROW(Sheet2!$A$2)+1),ROWS($1:1))),"")

where A2 houses an ID of interest you are looking for.