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!
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:
$D2
specifies the value to search for.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.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.FALSE
says to only return exact matches.