Course contents · Lookup functions

MATCH and INDEX

Open the practice in the trainer →

MATCH returns the relative position of a value in a range:

=MATCH(value, array, [match_type])
1 (default)largest value ≤ the lookup value, array sorted ascending
0first exact match, any order
-1smallest value ≥ the lookup value, array sorted descending

INDEX returns a value by row and column number: =INDEX(table, row, [column]).

INDEX + MATCH

The pair replaces VLOOKUP/HLOOKUP and does more: it can look to the left and on two axes at once.

=INDEX($B$2:$E$6, MATCH(H2,$A$2:$A$6,0), MATCH(H3,$B$1:$E$1,0))

Tasks in the trainer

  1. In H4 find the position of the branch from H2 in the list A2:A6.
  2. In H5 find the position of the month from H3 in the headers B1:E1.
  3. In H6 use one INDEX + MATCH formula to find the sales of branch H2 for month H3.
  4. Left lookup: in H14 find the code of branch G14 using the table G9:H11 (the code is left of the name — VLOOKUP can’t do it).
  5. In H16 find the month when London had its highest sales (INDEX + MATCH + MAX).

Solve the tasks in the trainer →

Check yourself

  1. What does =MATCH("Mar",{"Jan";"Feb";"Mar"},0) return?
  2. Which match type works with an unsorted list?