Before XLOOKUP arrived, anyone who needed sturdier lookups than VLOOKUP used the INDEX and MATCH pair (INDICE and CONFRONTA in Italian). It is still the most portable solution today: it works in every version of Excel, in LibreOffice Calc and in Google Sheets, and a file built this way opens anywhere without surprises. It is worth understanding properly, because each function on its own is simple and the combination is very powerful.
The two functions separately
INDEX: give me the item at that position
=INDEX(range, row, [column])
INDEX returns the content of a cell identified by its position. =INDEX(B2:B100, 5) returns the fifth value in the range, that is B6. With a two-dimensional range you give row and column: =INDEX(B2:M100, 5, 3) returns the cell on the fifth row and third column of the range.
On its own it looks of little use, because we usually do not know at which position the data sits. That is where MATCH comes in.
MATCH: tell me where it is
=MATCH(value, range, [type])
MATCH looks for a value in a single-row or single-column range and returns its position, not its content. If the code "X-204" sits in cell A13 within A2:A100, =MATCH("X-204", A2:A100, 0) returns 12.
The third argument plays the same role as VLOOKUP's fourth argument:
| Type | Behaviour | Requirement |
|---|---|---|
| 0 | exact match | none |
| 1 | largest value less than or equal | ascending order |
| -1 | smallest value greater than or equal | descending order |
If you leave it out it defaults to 1, so for codes and names you should always write 0.
The combination
Putting MATCH inside INDEX gives you a complete lookup: MATCH finds the row, INDEX returns the value on that row in the column you need.
=INDEX($D$2:$D$500, MATCH(A2, $A$2:$A$500, 0))
It reads like this: "in the price column, return the value at the same position where the code in A2 appears in the code column". The two columns are independent, so:
- you can look to the left, for example find a code starting from a description;
- you can insert or delete columns in between without breaking anything;
- the lookup range and the return range can even sit on different sheets, as long as they are the same height and start on the same row.
That last point is the constraint to remember: if one range starts on row 2 and the other on row 3, the result is shifted by one row and nobody notices.
Two-way lookup
INDEX accepts both a row and a column, so two MATCH functions let you look up the intersection of a table. Suppose products are in A3:A40, months in B2:M2 and sales in B3:M40:
=INDEX($B$3:$M$40, MATCH(P2, $A$3:$A$40, 0), MATCH(P3, $B$2:$M$2, 0))
With the product in P2 and the month in P3 you get the matching value. By changing P2 or P3, for instance through a drop-down list, you build a small lookup panel in a few minutes.
Lookup with several criteria
A frequent case: finding the price of an item in a given size, where neither column on its own is unique. A clean solution is to create a helper column joining the two fields, for example in E2 =A2&"|"&B2, and then look up the combined key:
=INDEX($D$2:$D$500, MATCH(H2&"|"&H3, $E$2:$E$500, 0))
The | separator avoids ambiguity: without it "AB" + "C" and "A" + "BC" would produce the same key. Recent versions offer solutions without a helper column, based on array formulas, but the explicit column has one advantage: anyone opening the sheet understands how the lookup works.
Using MATCH on its own
MATCH is useful without INDEX too:
- to check whether a value exists in a list, with
=ISNUMBER(MATCH(A2, List, 0)), which returns TRUE or FALSE; - to make the column index of an existing VLOOKUP dynamic, replacing the fixed number with a MATCH on the header row;
- to find the position of a maximum value, combining it with MAX:
=MATCH(MAX(B2:B50), B2:B50, 0).
Typical mistakes
- Forgetting the 0 in MATCH, with approximate results on unsorted data.
- Ranges of different heights for lookup and return.
- Unlocked ranges that slide when you copy the formula.
- Inconsistent data: numbers stored as text, trailing spaces, invisible characters.
In my work, when I have to hand a file to people using different software, I choose INDEX and MATCH precisely for this reason: it is the option least likely to cause surprises.
In short
- MATCH returns a position, INDEX the value found at that position.
- Together they look up in any direction and survive inserted columns.
- With two MATCH functions you look up the intersection of a row and a column.
- For several criteria, a key column with a separator is simple and transparent.
- It works in every version of Excel, in LibreOffice Calc and in Google Sheets.
