Extract common numbers

[BenElliott]

I'm trying to compare two columns of numbers and extract the numbers that are common to both.

Cell A2 contains the number of filled cells in column 'A'. Column 'B' has list 2.

I can use the following to extract the numbers but it leaves blank cells in the resultant column 'C': =IF(ISERROR(MATCH(A5,$D$5:$D$200,0)),"",A5)

If I use the following:=IF(ROWS(A$5:A5)>$A$2,"",INDEX($D$5:$D$200,AGGREGATE(15,6,(ROW($B$5:$B$200)-ROW($B$5)+1)/ISNUMBER(MATCH($B$5:$B$200,$A$5:$A$200,0)),ROWS($E$5:E5)))) I get the first common value in every cell of column 'C'

My head is spinning. Where am I going wrong?

Thanks for your patience.

Ben

 

[BenElliott]

AhoyNC and Marcelo,

Thanks, both. Both formulae worked beautifully. Thanks for helping out a brain be-fuddled person.

Ben

 

[BenElliott]

Thanks, too, Aladin.

 

[Aladin Akyurek]

Thanks, too, Aladin.

Note the difference though.

 

[BenElliott]

Oh, yes. I noticed that your solution needs Ctrl+Shift+Enter.

Plus, AhoyNC and Marcelo's solutions, while producing the same overall result present the same data source in a slightly different order in the results column. This will help me to see a little more clearly how the various methods are working.

Again, thanks for your all helping me out.

Ben

 

[Aladin Akyurek]

Oh, yes. I noticed that your solution needs Ctrl+Shift+Enter.

Plus, AhoyNC and Marcelo's solutions, while producing the same overall result present the same data source in a slightly different order in the results column. This will help me to see a little more clearly how the various methods are working.

Again, thanks for your all helping me out.

Ben

My suggestion skips duplicate common values: It creates a unique common set in ascending order.