can I use IF function to compare 2 columns

  • Thread starter Thread starter Karen271077
  • Start date Start date
K

Karen271077

Hi

I currently have 2 reports ... Both have peoples names in it. I would
like to compare the names in one report to the names in the other
report. If the names are the same I want the report to state ok, if
not it needs to state WRONG

Workbook 1

Column A Last Name
Colum B First Name
Column C Location
Column D - OK? WRONG? Is the last name in WB1 = the last name in WB2

Worksbook 2

Column A Last Name
Colum B First Name
Column C Location

PLEASE HELP ME .......:(
 
the easiest way is just to use the inbuilt test. To illustrate

In A1 enter Matt
In B1 enter Mat
in C1 enter =A1=B1
C1 will read false. If you change B1 to Matt, C1 will change to true
 
A simple IF comparison is only meaningful if you expect both lists to be
identical in length, order and content: this effectively traps "typing"
errors. If the lists are different lengths, then you will have to use another
approach and probably compare the combined last name/first name of both
workbooks to allow duplicate surnames.

One way is to create a "helper" column in each workbook (e.g column E)
containing the concatenation of last name & firstname, and then use (for
example) in WB1 in Col F:

=IF(COUNTIF([WB2.xls]Sheet1!$E:$E,E1),"OK","Wrong")

HTH
 
Hi Toppers,

You are indeed correct, both lists are different in size. Again eac
employee has 2 records, for the purpose of data inteegrety I need bot
records to reflect exactly the same name. Lets just work with on
column which is their last name. List 1 might say Cordova as a las
name and list 2 might say cordova salinas. Whenever i have an issu
like this my report needs to state false. I tried your formula bu
can't seem to work it out (is the condition case sensitive by an
chance
 
It is not case sensitive and will return FALSE if comparing "Cordova" vs
"Cordova Salinas" irrespective of case of either cell.

So I don't understand why it is not working for you.
 
Hi toppers ... now it's working :)
Now I want to take it one step further ...

So now I have compared

WS 1 Column A - Cordova
WS 2 Column A - Cordova Salinas - Result is displayed as WS1 Column
Wrong

Now I would like to display the name cordova Salinas in WS1 Column C s
that my people know the diffrenec
 
=IF(B1="Wrong",VLOOKUP("*"&A1&"*",ws2!A:A,1,0),"")

Or combine them in B1

=IF(COUNT(ws2!A:A,A1),"",VLOOKUP("*"&A1&"*",ws2!A:A,1,0))

This assumes that "Part" of the name exist i.e if "Cordova" is not in any
part of the list, it will error so use:

in C1

=IF(B1="Wrong",IF(ISNA(VLOOKUP("*"&A1&"*",ws2!A:A,1,0)),"No
match",VLOOKUP("*"&A1&"*",ws2!A:A,1,0)))

OR

in B1

=IF(COUNT(Sheet2!A:A,!A1),"",IF(ISNA(VLOOKUP("*"&A1&"*",ws2!A:A,1,0)),"No
match",VLOOKUP("*"&A1&"*",ws2!A:A,1,0)))

HTH
 
Back
Top