Spot the error... VLOOKUP

**emilyrose** · 11-22-2012, 06:51 AM

Hi,

I have a list of 1250 people. Every person belongs to a cost code, of which there are about 50. I am looking at two lists on two sheets. I'm trying to get all the data between the two lists to match. The two lists are similar but every row may not correspond with the same row on the other sheet.

I have uploaded a dummy example. I want to compare column B in sheets 1 and 2. I have completed a successful VLOOKUP for employee number, but that is a unique code. Is it possible if the data is not unique?

Any help you could give would be great. And an explanation as to why I can't get it work would be even better. CC example.xls

**martindwilson** · 11-22-2012, 07:22 AM

you have specified a single range of column b so vlookup can only look at column 1
=IF(VLOOKUP($B2,'HR current October'!$B$2:$B$4,1,FALSE)=VLOOKUP('HR current October'!$B2,'October SiP EG'!$B$2:$B$4,1,FALSE),"match","change")
however if there is no match you will get #n/a as this overrides the option to give back a false return
if you just want to see if columb b from one sheet is in the other 2 sheets
=IF(AND(COUNTIF('October SiP EG'!B2:B20,B2),COUNTIF('HR current October'!B2:B20,B2)),"match","change")