How to compare several columns...? [Solved/Closed]

Report
-
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018
-
Hello,

could anyone help me with this. I would like to compare two columns:

A B C D
Peter 4 John 3
John 3 Peter 5
Greg 2 Greg 6
Emily 8

I would like to see which Names I can not find in Column C that are present in Column A. This is easy, i do that with IF. But I would like to take it further, when there is a match, I would like excel to deduct the Number from Column B from the Number from Column D. For example I would have 4 for Greg (6 minus 2). How do I do that please?

4 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
"I would like to see which Names I can not find in Column C that are present in Column A"
did you forget to add "in the same row)

data is in rows 1 to 4
in D1 copy this formula

=IF(C1=A1,D1-B1,"NO")

copy this formula down.
modify formula for what happens the names inC and A are not the same.
thank you very much for your quick answer, I really appreciate it. Now, the formula you have provided is only true when the matching names are in the same row, in my example it would work since they are in the same row, however what if they are not, is there a formula for that too?
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
sorry in my last message I wrote "copy formula to D1" it shoiuld be E1

now if the same names are not in the same row copy this formula in E1 and copoy down

your data is like this (coloumns A to D on 4 rows)

Peter 4 Greg 3
John 3 Peter 5
Greg 2 John 6
Emily 8


in E1 the formula is

=D1-INDEX($B$1:$B$4,MATCH(C1,$A$1:$A$4,0),1)
and copy down
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018

I have embedded the above formula into the TRUE part of the IF function and it perfectly worked. I am very grateful for the advice and will visit the site regularly. This is really a value add service that is being provided here!

Thank you

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!