can I use IF function to compare 2 columns

[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 .......

 

[Mallycat]

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

 

[Guest]

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

 

[Karen271077]

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

 

[Guest]

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.

 

[Karen271077]

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

 

[Guest]

=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