Excel formula to match multiple dates across 2 col

问题:

I need a formula that will match dates across column A and B in the below table. There are over 100000 results to match in each of my tables.

If specific date appears in both columns, then it returns with 'Yes' statement. If it only appears in one column, than 'No'.

I tried to use the below formula but it doesn't return the correct output:

=IF(COUNTIF($B:$B, $A:$A)=0, "Yes", "No")

The correct results should be as in column C.

Thanks in advance.

回答1:

This will do the trick, name what you want to display.

The logic is this: It counts if the criteria exist in Column A. When no matches is found (=0) it give "Don't exist in Column A".

=IF(COUNTIF($A:$A,B2)=0, "Don't exist in Column A","Yes")

So in your case when we apply the formula it will look like this:

=IF(COUNTIF($A:$A;B3)=0, "No", "Yes")

Notice in the picture I use ";" as delimiter since I have a Nordic Excel version. US will probably use ",".

回答2:

A Faster Excel Version

Referring to the formula =IF(COUNTIF($A:$A,B2)=0,"No","Yes")

Looks like the same but its much faster.

=IF(ISERROR(MATCH(B1,$A:$A,0)),"No","Yes")

On first glance, match should be faster since it cannot count, but of course the reason is probably in the following logic:
COUNT searches the whole range to calculate the COUNT and then the IF 'decides' if it is 0 or not.
MATCH searches the range only until it finds the MATCH and when found the IF 'decides via the ISERROR function' if it is TRUE or FALSE. If the matches would be at the bottom of the range, maybe the speed gap could be disregarded, but they aren't.

Rearranging the formulas like

=IF(NOT(ISERROR(MATCH(B1,$A:$A,0))),"Yes","No")

or

=IF(COUNTIF($A:$A,B2)<>0,"Yes","No")`

doesn't change a thing. MATCH still searches until a match is found, and COUNT still counts in the whole range.

So to conclude:

MATCH is the 'Excel' way to go.

回答3:

If you need result when the Audit date column to match with date column and date column to match with Audit date then use

=if((countif(B:B,A2)+countif(A:A,B2))<>0,"Yes","No")

If you need result when Audit date match with the date column then use:

=if((countif(A:A,B2))<>0,"Yes","No")