Comparing multiple columns

[jjackson]

I have a user with an issue that I am struggling to solve.

They have data that came from two sources with discrepancies. The data on each sheet references Emp ID, Name and Location Code (along with other information not relevant to the issue).
Sample attached. The list of employees are not identical.

Looking to compare the location code for each EMP ID in first three columns to the same EMP ID info in the second set of columns and identify discrepancies. Example EMP ID 568701... first set shows different location code from the second set.

I am sure it will involve vlookups (which i just cannot seem to ever get right). Any help appreciated.


List sample.xlsx

[wfm007]

In cell F2 use & drag down:

Please Login or Register  to view this content.

In cell G2 use & drag down:

Please Login or Register  to view this content.

[Harribone]

Hi,

I've modified your sample file and attached for you.
The two columns inbetween your two sets of data use the VLOOKUP function to find the data in the second table for each entry on the first table. If the EMpID doesn't exist in the second table then "Not Found" is displayed. Otherwise "OK" or "ERROR" are shown where OK refers to matching data for that particular field and ERROR is a discrepancy.

When applying to yor actual work you just need to set up the formula for the first row in each column then drag down. Note the use of $ in the formula is so that this bit will always be the same when the copy down.