Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a workbook with a sheet that is formatted like this:

| 1234 | Name 1 |
| 2345 | Name 2 |
| 3456 | Name 3 |

There's then another sheet with data like this:

| Hours | Employee ID |
| 18    | 1234        |
| 8     | 2345        |
| 2     | 3456        |

Is there an automated way in Excel to automatically replace all of the employee ID numbers in the second sheet with their corresponding name? Like "1234" -> "Name 1".

share|improve this question
3  
This is more of a job for Microsoft Access than Excel. If you are going to be doing a lot of vlookups I would suggest switching to a database instead of a spreadsheet. – wbeard52 Jul 12 '12 at 13:29

2 Answers

up vote 5 down vote accepted

The easiest way to do what you are looking at is to add an additional column to the sheet with hours and employee ID and the do a VLookup in the new column. Microsoft has help on the VLookup at http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx

A couple of important things to note - the list on the first sheet must be sorted by ID (the value you are looking up as a VLookup stops when you hit the next value).

The formula for your case would be approximately (assuming columns are next to each other and start at column A and row 1 has titles)

=vlookup(B2,Sheet1!A1:B200,2)

  • B2 is the field you are looking up (ID)

  • Sheet1!A1:B200 is cells on other sheet containing data - exclude headings

  • 2 is the second column in the other sheet.

share|improve this answer
2  
On a side note, if you set the fourth parameter of =VLOOKUP= to =FALSE=, the table doesn't need to be sorted because it does an exact match instead of a range lookup. – dsolimano Jul 12 '12 at 12:39
3  
That 4th parameter of FALSE definitely should be set, not just to avoid sorting, but because otherwise it will return the "closest match". Since you are trying to replace exact matches only, this would cause confusion if it returned a completely unrelated person when there wasn't an exact match. Using FALSE will cause it to return #N/A so you can identify the unmatched entries. – techturtle Jul 12 '12 at 15:45
up vote 2 down vote

I can't comment here, but in addition to the comments on Alain King's answer.

For long term VLookup solutions, it can be helpful to have a larger general range that includes the actual range.

In this circumstance I'd do:

=vlookup(B2,Sheet1!A:B,2,FALSE)

In the Alain's example, this will have the exact same result as the his formula. The only difference is that if data is added in row 201 or beyond the formula will automatically search those rows as well.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.