Formula to compare 2 columns and count missing values

Mikey150
Posts
1
Registration date
Wednesday April 20, 2022
Status
Member
Last seen
April 20, 2022
- Updated on May 10, 2022 at 11:29 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- May 5, 2022 at 08:07 PM
Hello,

I have an Excel spreadsheet where I want to compare values between two columns A and b and to see if any values are missing in column b.

Are you able to give a formula as the formula I am using keeps give me “0” for every row and I know that’s incorrect as I’ve checked with a sort and subtract formula which shows 2 values are missing.

2 replies

Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
147
Apr 22, 2022 at 04:36 PM
Mikey150, good afternoon.

Suppose:

1st Column: A1:A100

2nd Column: D1:D100

Try to use:

B1 -->
=IF(COUNTIF($D$1:$D$100;A1)=0; "...Missing..."; "")


Copy down as necessary.

Please, tell us if it works as you want.

I hope it helps.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
May 5, 2022 at 08:07 PM
I think MATCH would be better


in column C2 you can add some thing like this (assuming row 1 is header of sheet)

=IFERROR(MATCH(A2, B$2:B$1000,0),"Not Found")
0