VLOOKUP for employee salary band
BeginnerHR teams often use lookup tables to turn short codes into clear labels, like mapping a job level to a salary band. VLOOKUP is a quick way to do that for many rows at once.
VLOOKUP syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: the value you want to find (here, the job level code)
- table_array: the reference table that contains the codes and the labels
- col_index_num: which column of the reference table to return (1 = first column, 2 = second column)
- range_lookup: whether you want an exact match or an approximate match
In this sheet, each employee's job level is in column B, and the HR classification table is in E2:F5.
The classification table is intentionally not sorted, which is why an exact match matters here.
Your task
- Click cell C2.
- Write a VLOOKUP formula that looks up the job level in B2 in the classification table in E2:F5.
- Return the salary band from the table.
- Lock the classification table range so it stays fixed when you copy the formula down.
- Use an exact match, not an approximate match.
- Copy the formula down through C11.
When you're done, the Salary band column should be filled in for all 10 employees.
Need some help?
Hint 1
For VLOOKUP, the last argument (range_lookup) controls exact vs approximate matching. For an exact match, use FALSE (or 0).
Hint 2
Make sure your return column is the Salary band column in the lookup table (the column to the right of Job level).
Hint 3
When you highlight the table_array, lock it with $ (absolute cell reference, for example A1 => $A$1) so the range stays fixed when you fill the formula down.
VLOOKUP for employee salary band
BeginnerHR teams often use lookup tables to turn short codes into clear labels, like mapping a job level to a salary band. VLOOKUP is a quick way to do that for many rows at once.
VLOOKUP syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: the value you want to find (here, the job level code)
- table_array: the reference table that contains the codes and the labels
- col_index_num: which column of the reference table to return (1 = first column, 2 = second column)
- range_lookup: whether you want an exact match or an approximate match
In this sheet, each employee's job level is in column B, and the HR classification table is in E2:F5.
The classification table is intentionally not sorted, which is why an exact match matters here.
Your task
- Click cell C2.
- Write a VLOOKUP formula that looks up the job level in B2 in the classification table in E2:F5.
- Return the salary band from the table.
- Lock the classification table range so it stays fixed when you copy the formula down.
- Use an exact match, not an approximate match.
- Copy the formula down through C11.
When you're done, the Salary band column should be filled in for all 10 employees.
Need some help?
Hint 1
For VLOOKUP, the last argument (range_lookup) controls exact vs approximate matching. For an exact match, use FALSE (or 0).
Hint 2
Make sure your return column is the Salary band column in the lookup table (the column to the right of Job level).
Hint 3
When you highlight the table_array, lock it with $ (absolute cell reference, for example A1 => $A$1) so the range stays fixed when you fill the formula down.