21

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!

Rick Davies
  • 713
  • 9
  • 22
Nicole Smith
  • 219
  • 1
  • 2
  • 3
  • 6
    I dont think this is a duplicate. This question is about looking up corresponding values, the question that this points to as a duplicate is about looking up corresponding locations. They are similar, but definitely different, which shows both in the question itself and in the answers. – Leo Sep 01 '17 at 12:12

2 Answers2

41

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.
lc.
  • 113,939
  • 20
  • 158
  • 187
  • 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? – Leo Sep 01 '17 at 12:08
  • @Leo could it be related to https://superuser.com/questions/496614/function-argument-delimiter-excel-2011-for-mac-os-x ? In my version (Windows, US English) it's always been a comma. – lc. Sep 01 '17 at 16:26
5

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

=VLOOKUP(A1, Sheet2!A:B, 2,false)
GenericJon
  • 8,746
  • 4
  • 39
  • 50
Geo
  • 12,666
  • 4
  • 40
  • 55