Excel - Vlookup

marka87uk

I have two sheets, both with a table on them with two columns - branch number and employee number.

For simplicity I'll just say the second sheet is the most updated version and so I need to work out if an employee has transferred to a different branch on the first sheet. I've already removed employees who have left so you don't need to consider that...

Basically I guess I need to lookup the employee number using a vlookup on the second sheet, and place the most updated branch number for that employee on the first sheet into a third column... then I need to use a formula in a fourth column to check if the most updated branch number and the one already on the first sheet is different?

Is there an easier way to do this? It goes a bit above my head sometimes!

Chippy_Minton

Note that VLOOKUP searches for a value in the leftmost column of a table. Since you want to search on employee number, the lookup table on Sheet2 must have the employee number as the first column in the table (and then branch number). For consistency, I've put the columns on Sheet1 in the same order in this example.

On Sheet1, put the following formula in C2 to lookup the employee number in A2 and return the branch number:
=VLOOKUP(A2,EmployeeBranchTable,2,FALSE)

EmployeeBranchTable is the named range for the table on Sheet2.

On Sheet1, put the following in D2 to check whether the branch numbers in B2 and C2 are different:
=IF(B2=C2,"", "Different branch")

Note - I've assumed that Row 1 is used for column headings on both sheets.