Learn Excel
EN
Sets how numbers, dates and formulas look, so everything works just like your own Excel.

Home › Learn Excel › Lesson 9

INDEX and MATCH in Excel

Combine INDEX and MATCH for lookups to the left, and build references from text with INDIRECT.

  1. INDEX + MATCH. MATCH finds the row of a value, and INDEX returns what's in that row of another column. Together they look up anything. Find the department of Dina in F2
  2. Why INDEX + MATCH?.
  3. References from text: INDIRECT. INDIRECT turns text like B4 into a real cell reference. Change the text and the formula follows. Show the value of the cell whose address is in D2, in E2
  4. Two conditions with a helper key. VLOOKUP looks for one value. For two conditions, column A joins them into one key, like Ben-South.
    In the formula you build the same key with &. Find Ben's sales in the South region in H2
  5. Your turn: INDEX + MATCH. MATCH finds the row of a value, and INDEX returns what's in that row of another column. Together they look up anything. Find the department of Dina in F2

← All lessons