Lookup FunctionsAdvanced

INDEX-MATCH Combination

Use INDEX and MATCH functions together for flexible lookups.

Task:

You need to look up employee salaries based on their names. Use INDEX-MATCH combination to find salaries, which offers more flexibility than VLOOKUP.

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of INDEX MATCH

0/4

The same formula in the other shapes it takes at work.

  1. INDEX-MATCH Combination

    Use INDEX and MATCH functions together for flexible lookups.

    CoreAdvanced
  2. INDEX/MATCH to the left

    Return a column that sits before the one you search — which VLOOKUP cannot do.

    Advanced
  3. INDEX/MATCH with MAX: who is top?

    Find the highest figure, then the name beside it.

    Advanced
  4. INDEX/MATCH with an approximate MATCH

    MATCH type 1 finds the band; INDEX returns its label from any column.

    Advanced

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

9 rows × 3 columns2 cells you fill in
ABC
1EmployeeDepartmentSalary
2JohnSales55000
3MaryMarketing62000
4TomIT68000
5SarahHR58000
6
7Lookup EmployeeFound Salary
8Mary
9Tom

Functions used here

What this exercise teachesMay contain the answer

INDEX-MATCH is more versatile than VLOOKUP because it can look up values in any direction and the lookup column doesn't need to be the first column.