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 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
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.
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?
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
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
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