Creating a list of matching column cells

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:23 PM GMT

Question

Excel demo

QUESTION: I have two columns each with unique values. I want to create a third column with a list of the two first column's matching cells.

I have found out how to count the amount of matching cells in the E1 cell: =SUMPRODUCT(--ISNUMBER(MATCH(A$2:A$10;B$2:B$10;0))*(A$2:A$10<>"")). However I am lost in how to add a formular that will show the matching cells in the green area D2:D10.

Any idea?

Excel demo


ANSWER: I would do this with an array formula

=IF(COUNTIF($A$2:$A$10,$B$2:$B$10)>0,INDEX($B$2:$B$10,SMALL(IF(COUNTIF($A$2:$A$10,$B$2:$B$10)>0,ROW($A$2:$A$10),99999),ROW()-1),1),"")

To enter this, select d2:D10 as a block, enter my formula and press Ctrl Shift Enter

---------- FOLLOW-UP ----------

QUESTION: I have done as you suggested, but in the example the result does not include the blue value (see attached image).

What am I missing? Fyi, in mysettings I have to use semicolon and not comma to separate the code.

Answer

Argh- sorry - I spotted the error - when I first read the question I thought I saw data from row1 - which of course isn't right - I had partially fixed the formula when I copied it over but not totally - all references in the formula to ROW() should be ROW()-1

=IF(COUNTIF($A$2:$A$10,$B$2:$B$10)>0,INDEX($B$2:$B$10,SMALL(IF(COUNTIF($A$2:$A$10,$B$2:$B$10)>0,ROW($A$2:$A$10)-1,99999),ROW()-1),1),"")

I've kept it as commas as I wanted to test that it worked - but in your formula, just edit the row(a2 etc to ROW($A$2:$A$10)-1 and press ctrl shift enter to accept the change

Advertisement

©2021 eLuminary LLC. All rights reserved.