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!

Products	Location
123456	SLFL
123456	BKRM
123456	SLFL
555555	SLFL
555555	SLFL
555555	SLFL
555555	SLFL
555555	BKRM
555555	BKRM
123456	SLFL
123456	BKRM

<tbody>
</tbody>

 

[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.