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 |
0 | first exact match, any order |
-1 | smallest 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
- In H4 find the position of the branch from H2 in the list A2:A6.
- In H5 find the position of the month from H3 in the headers B1:E1.
- In H6 use one INDEX + MATCH formula to find the sales of branch H2 for month H3.
- 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).
- In H16 find the month when London had its highest sales (INDEX + MATCH + MAX).
Solve the tasks in the trainer →
Check yourself
- What does =MATCH("Mar",{"Jan";"Feb";"Mar"},0) return?
- Which match type works with an unsorted list?