CODEWITHSUNDEEP
excelexcel-formulamatch
Dave
Dave

Reputation: 1

Excel - return matches without blanks

in Excel, I have a list in column A and a list in column B, and I want to find exact matches and return them in column C. I’m using IF(ISNA(MATCH(A1,$B$1:$B$1000,0)),””,INDEX($B$1:$B$1000,MATCH(A1,$B$1:$B$1000,0))). However I want to do this such that in column C the matches are listed sequentially, e.g. 1st match returns in C1, 2nd in C2, third in C3 etc, rather than the matches being listed in the same row as the column A entry, as this process gives me a lot of blank cells in column C. Is this possible to do just with a formula? Any ideas greatly appreciated!!

Upvotes: 0

Views: 143

Answers (1)

mark fitzpatrick
mark fitzpatrick

Reputation: 3310

If you have Excel 365, you can do:

=FILTER($A$1:$A$1000,ISNUMBER(MATCH($A$1:$A$1000,$B$1:$B$1000,0)))

Upvotes: 0

Related Questions