Exceljet

Quick, clean, and to the point

VLOOKUP from another sheet

Excel formula: VLOOKUP from another sheet
Generic formula 
=VLOOKUP(lookup,sheet!range,column,match)
Summary 

Using VLOOKUP from another sheet is very similar to using VLOOKUP on the same sheet. In the example shown, the formula in F5 is:

=VLOOKUP(B5,Sheet2!$B$5:$C$104,2,0)

VLOOKUP retrieves the correct building for each employee from Sheet2 into the table on Sheet1.

Explanation 

In this example, we have a table of employee locations like this on Sheet2:

We use VLOOKUP to lookup each person's building from Sheet2

On Sheet1, we retrieve the building location for each team member using this formula:

=VLOOKUP(B5,Sheet2!$B$5:$C$104,2,0)

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 an absolute reference, so that the range does not change as the formula is copied down:

Sheet2!$B$5:$C$104 // includes sheet name

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.