**SOLVED**Extracting Unique Values Based On The Value In A Cell

Good morning,

I am hoping someone can help me out with a formula I'm having trouble with. In the attached, Sheet 1 contains two columns. Column A contains reference numbers that may be duplicated many times. Column B contains the correlating associate reference numbers that are related to column A's reference numbers and may contain duplicates as well.

What I am hoping to do in sheet 2 is key in the reference number in cell A2 and then have a formula in column B that will pull back, without duplicates, the unique associate reference numbers found in column B of sheet 1. Hope that makes sense and I'm sure I'm just missing something very simple but haven't had enough coffee yet. I have a formula in the works but it's not extracting everything quite correctly. Any help would be appreciated.

Thanks in advance!

You almost got it.

=IFERROR(INDEX(Sheet1!$B$2:$B$11,SMALL(IF(Sheet1!$A$2:$A$11=Sheet2!$A$2,ROW(Sheet2!$A$2:$S$11)-1),ROWS($B$2:B2))),"")

Correction in red

To get the unique associated ref# associated with 9611, try this.....

On Sheet2
In B2 (Array Formula)

Please Login or Register  to view this content.

and copy down.

Thank you for the feedback!

If I am not wrong you are interested in unique associated ref# for 9611 (being in A2) not all the associated ref# for 9611 including duplicates. Right?
If so, the formula I suggested will return only the unique ref# without duplicates.

Thanks for the feedback.

rlh06,

Please don't write SOLVED in the title. Rather select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.