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