Previous Topic Next Topic

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.
Related:

2 replies

Answer 1 / 2
Mazzaropi Posts 1983 Registration date Monday August 16, 2010 Status Contributor Last seen February 27, 2023 146
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
Answer 2 / 2
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
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