Excel - find cell with same value in another worksheet and enter the value to the left of it

Question

I have a report that is generated in Excel which contains an employee's number, but not his/her name. Not every employee will be on this worksheet on any given day.

In a 2nd worksheet I have a list of all employees' numbers and names.

I want a formula in the first worksheet that looks for the same value (the employee number) on the 2nd workbook and then enters the value of the cell to the RIGHT of that (the employee's name) on the first workbook. Is there a way to do this? Thanks!

Solution 1

The easiest way is probably with VLOOKUP(). This will require the 2nd worksheet to have the employee number column sorted though. In newer versions of Excel, apparently sorting is no longer required.

For example, if you had a "Sheet2" with two columns - A = the employee number, B = the employee's name, and your current worksheet had employee numbers in column D and you want to fill in column E, in cell E2, you would have:

=VLOOKUP($D2, Sheet2!$A$2:$B$65535, 2, FALSE)

Then simply fill this formula down the rest of column D.

Explanation:

• The first argument $D2 specifies the value to search for.
• The second argument Sheet2!$A$2:$B$65535 specifies the range of cells to search in. Excel will search for the value in the first column of this range (in this case Sheet2!A2:A65535). Note I am assuming you have a header cell in row 1.
• The third argument 2 specifies a 1-based index of the column to return from within the searched range. The value of 2 will return the second column in the range Sheet2!$A$2:$B$65535, namely the value of the B column.
• The fourth argument FALSE says to only return exact matches.

Solution 2

Assuming employee numbers are in the first column and their names are in the second:

=VLOOKUP(A1, Sheet2!A:B, 2,false)

Comments

[Leo]

Great explanation, this helped me a lot. However, my excel seperates function input arguments with semicolons. Dont know if that is a version issue or a typo?

[lc.]

@Leo could it be related to superuser.com/questions/496614/… ? In my version (Windows, US English) it's always been a comma.