Cross-sheet VLOOKUP for employee data
AdvancedEmployee data often lives on a dedicated roster sheet while project managers track assignments on a separate sheet. Instead of copying names and rates by hand, you can use VLOOKUP with a cross-sheet reference to pull data automatically.
To reference a range on another sheet, prefix it with the sheet name and an exclamation mark. For example, Roster!A:D refers to columns A through D on the Roster sheet.
What you need to do:
The Roster sheet contains employee IDs, names, departments, and hourly rates. On the Assignments sheet, each row has a project name and an employee ID already filled in. Use VLOOKUP to fill in the Name and Hourly Rate columns by looking up each employee ID on the Roster sheet.
Example:
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
Write your formulas in the empty cells on the Assignments sheet (columns C and D, rows 2 through 5).
Need some help?
Hint 1
The table_array for a cross-sheet VLOOKUP uses the format SheetName!Range, for example Roster!A:D.
Hint 2
The col_index_num counts from the left edge of your table_array: 2 for Name, 4 for Hourly Rate.
Hint 3
Use absolute references on the table_array (Roster!$A:$D) so the formula copies correctly to other rows.
Cross-sheet VLOOKUP for employee data
AdvancedEmployee data often lives on a dedicated roster sheet while project managers track assignments on a separate sheet. Instead of copying names and rates by hand, you can use VLOOKUP with a cross-sheet reference to pull data automatically.
To reference a range on another sheet, prefix it with the sheet name and an exclamation mark. For example, Roster!A:D refers to columns A through D on the Roster sheet.
What you need to do:
The Roster sheet contains employee IDs, names, departments, and hourly rates. On the Assignments sheet, each row has a project name and an employee ID already filled in. Use VLOOKUP to fill in the Name and Hourly Rate columns by looking up each employee ID on the Roster sheet.
Example:
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
Write your formulas in the empty cells on the Assignments sheet (columns C and D, rows 2 through 5).
Need some help?
Hint 1
The table_array for a cross-sheet VLOOKUP uses the format SheetName!Range, for example Roster!A:D.
Hint 2
The col_index_num counts from the left edge of your table_array: 2 for Name, 4 for Hourly Rate.
Hint 3
Use absolute references on the table_array (Roster!$A:$D) so the formula copies correctly to other rows.