问题描述:

I have a spread sheet with two tables:

I want to find every "row" in table1 that exists as an exact match in table2 using a formula. For example, you can see I have one row in the "matches" table to the right.

So far, I can check for exact matches on a per row basis(this is an Array formula entered using ctrl+shift+enter):

=AND(EXACT(A3:B3,D3:E3))

How can I do that for the entire range in Excel?

网友答案:

Using a helper column. In F3 put this formula:

=IF(COUNTIFS(A:A,D3,B:B,E3)>0,MAX($F$2:F2)+1,"")

and copy down. It will fill all duplicates with an increasing number.

Then you can use this formula in G3:

=IFERROR(INDEX(D:D,MATCH(ROW(1:1),$F:$F,0)),"")

Copy it over one column and down sufficient for your needs.

It will populate the list with all duplicates.

相关阅读:
Top