Handling duplicated values with VLookup or similar?

[izzywhizzy]

Hi,

I have two excel sheets and need to lookup data in one and return a value from the other, however there is a complication which I haven't been able to solve so far.

Sheet one has Item Code in col A.
Sheet two also has Item Code in Col A, and Location in Col B.
In both sheets there may be duplicate Item Codes on many rows, but all Locations are unique.

Sheet One

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]ITEM CODE[/TD]
[TD]LOCATION[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]245[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]288[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]600[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]600[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet Two

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]CODE[/TD]
[TD]LOCATION[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]245[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]245[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]288[/TD]
[TD]F[/TD]
[/TR]
</tbody>[/TABLE]

I need to lookup each Item Code listed in sheet one, and if it is found in sheet two then return the Location from sheet two, into sheet one.
This works fine with a simple VLookup where there is only one unique Item Code. However, where there are duplicate Item Codes, the same Location is returned for all rows, whereas I need all the different Locations to be returned.


Data I wish to have returned into Sheet one:



[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]ITEM CODE[/TD]
[TD]LOCATION[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]245[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]288[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]600[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]600[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Using the example data above, when looking up Item Code 001 it should return Locations A and B each on different lines, where VLookup is returning Location A and A.
** I do NOT need Location C to be returned as there is not a third instance of the Item Code 001 to match it against.
** I DO need the Locations to be returned in the order found, working down the sheet.

When looking up Item Code 245 it should return only D (being the first Location found for that Item Code).
When looking up Item Code 288, it should return F.
When looking up Item Code 600 nothing will be returned.

As you can see, the Item Code data is not the same in each Sheet and the number of duplicated Item Codes varies.


Thanks in advance for any advice.

 

[Aladin Akyurek]

In B2 of Sheet1 control+shift+enter, not just enter, and copy down:

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

 

[izzywhizzy]

Hi Aladin
Thanks so much for your help, that has worked just fine!

 

[Aladin Akyurek]

Hi Aladin
Thanks so much for your help, that has worked just fine!

Glad to hear that. Thanks for the update.