Pull the Manager × Senior band off the salary matrix
Comp benchmarking opens Friday. The salary-band matrix lives on the left — 5 titles down column A, 4 tiers across row 1. The query mini-table on the right has the title in G2 and the tier in H2. Return the matching salary in I2 so HR can pull any title × tier combo without manually scanning the table.
Two-way INDEX/MATCH lookup
Practice using INDEX/MATCH to solve a real hr & operations problem. By the end, you’ll know when to reach for it and how to structure the arguments correctly.
Comp benchmarking has the salary-band matrix on the left — job titles down column A, experience tiers across row 1. The query mini-table on the right has the title in G2 and the tier in H2. Return the band salary in I2 so HR can pull any title × tier combination without manually scanning the table.
In I2, return the salary at the intersection of the title in G2 and the tier in H2. Use INDEX with two MATCH calls — one against column A for the row position, one against row 1 for the column position. Exact match for both.
INDEX/MATCH is the two-function lookup combo that beats VLOOKUP at flexibility. MATCH finds the position of a value in a single column, then INDEX returns the cell at that position from another range. It can look left, right, up, or down, and survives column inserts that would break VLOOKUP.
INDEX(return_column, MATCH(lookup_value, lookup_column, 0))Try the formula first. Hints cost CP for a reason.