Explanation
In this example, we have a table of employee locations like this on Sheet2:
On Sheet1, we retrieve the building location for each team member using this formula:
The lookup value is the employee ID, from cell B5.
For the table array, we use the range $B$5:$C$104 qualified with a sheet name, and locked as anabsolute reference, so that the range does not change as the formula is copied down:
This is the only difference from a normal VLOOKUP formula the sheet name simply tells VLOOKUP where to find the lookup table range, B5:C104.
Finally, column number is 2, since the building names appear in the second column, and VLOOKUP is set to exact match mode by including zero (0) as the last argument.
This ensures that we get the correct building for each team member and a #N/A error if for some reason the id is not found in the location table.
Related formulas
VLOOKUP from another workbook
Get employee information with VLOOKUP
Merge tables with VLOOKUP
VLOOKUP two-way lookup
VLOOKUP calculate grades
VLOOKUP without #N/A error
Dynamic worksheet reference
Related functions
VLOOKUP Function
The Excel VLOOKUP function is used to retrieve information from a table using a lookup value.
The lookup values must appear in thefirstcolumn of the table, and the information to retrieve is specified bycolumn number.
VLOOKUP supports approximate and exact matching…
Related videos
How to use VLOOKUP
How to replace nested IFs with VLOOKUP
How to use VLOOKUP for approximate matches
Why VLOOKUP is better than nested IFs
