Look up a manager with INDEX+MATCH
IntermediateIn HR and payroll work, you often need to pull the right manager name into a form or workflow step. The tricky part is that your lookup value isn’t always in the first column.
In this sheet, the roster is set up with Manager in column A and Employee in column B. That layout is common in exports, but it means a left-to-right lookup (like basic VLOOKUP) won’t fit the problem.
INDEX + MATCH solves this by:
- Using MATCH to find the row where an employee name appears
- Using INDEX to return the manager from the matching row
Your task
Fill in the missing manager names for the three pay change requests:
- Enter an INDEX-MATCH formula in C12 that looks up the manager for the employee in B12.
- Copy the formula down through C14.
Tip: Use exact match in MATCH, and lock the roster ranges so your formula copies cleanly.
Need some help?
Hint 1
MATCH should search the Employee column (the roster’s employee names) and return a position using exact match (0).
Hint 2
Your INDEX return range is the Manager column, and it should align row-for-row with the MATCH lookup range.
Hint 3
Lock the roster ranges with $ so they don’t shift when you fill the formula down from C12 to C14.
Answer
Exercise
Look up a manager with INDEX+MATCH
IntermediateIn HR and payroll work, you often need to pull the right manager name into a form or workflow step. The tricky part is that your lookup value isn’t always in the first column.
In this sheet, the roster is set up with Manager in column A and Employee in column B. That layout is common in exports, but it means a left-to-right lookup (like basic VLOOKUP) won’t fit the problem.
INDEX + MATCH solves this by:
- Using MATCH to find the row where an employee name appears
- Using INDEX to return the manager from the matching row
Your task
Fill in the missing manager names for the three pay change requests:
- Enter an INDEX-MATCH formula in C12 that looks up the manager for the employee in B12.
- Copy the formula down through C14.
Tip: Use exact match in MATCH, and lock the roster ranges so your formula copies cleanly.
Need some help?
Hint 1
MATCH should search the Employee column (the roster’s employee names) and return a position using exact match (0).
Hint 2
Your INDEX return range is the Manager column, and it should align row-for-row with the MATCH lookup range.
Hint 3
Lock the roster ranges with $ so they don’t shift when you fill the formula down from C12 to C14.