VLookup or Index Match Multiple Specific Text

[habibwfm]

Really hoping I can get some help with my issue.

I have a bunch of Products in Column C of which many are duplicated. In Column D I have the locations of the IDs of which there can be two.

I want to know when an ID is in both locations but vlookup and index match only shows me the first instance and not the second unique instance.

Below is a sample of my data. My actual data is over 30000 rows and can be longer or shorter in other instances.

Thanks!

[TABLE="width: 250"]
<tbody>[TR]
[TD]Products[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]SLFL[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]BKRM[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]SLFL[/TD]
[/TR]
[TR]
[TD]555555[/TD]
[TD]SLFL[/TD]
[/TR]
[TR]
[TD]555555[/TD]
[TD]SLFL[/TD]
[/TR]
[TR]
[TD]555555[/TD]
[TD]SLFL[/TD]
[/TR]
[TR]
[TD]555555[/TD]
[TD]SLFL[/TD]
[/TR]
[TR]
[TD]555555[/TD]
[TD]BKRM[/TD]
[/TR]
[TR]
[TD]555555[/TD]
[TD]BKRM[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]SLFL[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]BKRM[/TD]
[/TR]
</tbody>[/TABLE]

 

[DonnyF]

What is the purpose of having duplicates?

 

[Rob65]

If products are in column A, and Locations in column B, try using the frequency function within an array:

=SUM(--(FREQUENCY(IF($B$2:$B$14<>"",IF($A$2:$A$14=A2,MATCH($B$2:$B$14,$B$2:$B$14,0))),ROW($B$2:$B$14)-ROW(B2)+1)>0))

I have made up a data set and applied this formula to column C and it will return either 1 or 2 depending on whether there are one or two locations. It is an array, so must be entered by ctrl+shift+enter. It could be slow if you are looking over 30,000 rows.