Vlookup for duplicate values in multiple columns

[Manjunath S]

Hi,
I need a help with vlookup formula. I tried a normal vlookup formula but didn't work. Hope anyone in the forum will help me out. I have attached a workbook which contains 2 sheets. I need a formula which looks up for part # with corresponding qty and retrieve lot #.
Thank you,
Manjunath

 

[tomas]

Hi

find attached

 

[Manjunath S]

Hi Tomas,
Thanks for the quick response. But I see there is a duplicate value in the lot # column which I dont need. 24th & 25th line has has the same lot # HMW256U but I need the lot # in the 25th Line to be HMW256T. Hope you got my point. I shouldn't get the repeated lot#s as lot # is the unique value.
Thank you once again.