How to compare several columns...?

Solved/Closed
György - Jan 1, 2012 at 02:14 PM
DG83
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018
- Jan 2, 2012 at 02:36 AM
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

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Jan 1, 2012 at 09:08 PM
"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.
0
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?
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Jan 2, 2012 at 12:36 AM
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
0
DG83
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018

Jan 2, 2012 at 02:36 AM
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
0