Two functions that work as a pair. MATCH tells you where something is — it returns a position number. INDEX tells you what is at a position — it returns a value. Combined, they solve a problem VLOOKUP and XLOOKUP handle poorly: a two-dimensional lookup — finding a value at the intersection of a row and a column.
Classic lookup group
2D lookup — row × column
Training file: Match-index.xlsx
Two Functions — One Job Each
They do opposite things. MATCH converts a value to a position. INDEX converts a position to a value.
MATCH
Value → Position
You give it a value and a range. It tells you which position (row or column number) that value occupies.
"May"
→
5 (5th row)
"Disk Drives"
→
1 (1st column)
INDEX
Position → Value
You give it a range and a position (row number, column number). It returns whatever value sits at that intersection.
Row 5, Col 1
→
6,204
📌
Why use both together?
MATCH alone gives you a number — not very useful on its own. INDEX alone needs numbers you'd have to type manually. Combine them: let MATCH calculate the position, feed it directly into INDEX, and the result updates automatically whenever the selection changes.
MATCH — and its Default Problem
MATCH has the same default issue as VLOOKUP — approximate match unless you explicitly add 0. XMATCH is the modern version that fixes this.
= MATCH(lookup_value,lookup_array,[match_type])
lookup_valueThe value to find — e.g. the selected month A3 or product A4.
lookup_arrayThe row or column to search in. For months: the months column. For products: the products header row.
[match_type]Default is 1 (approximate, ascending) — same trap as VLOOKUP. Always write 0 for exact match unless you specifically need approximate. If omitted, results can be silently wrong.
MATCH vs XMATCH
⚠️ MATCH — requires the zero
Find position of "May" in the months column
= MATCH(A3, months_col, 0)
Forget the 0 → wrong result, no error warning.
✓ XMATCH — exact match by default
Same result, safer by default
= XMATCH(A3, months_col)
No 3rd argument needed. Exact match is the default.
💡
MATCH and XMATCH return the same thing
Both return a position number — an integer. That number is what gets passed to INDEX. You can use either one inside the INDEX formula; XMATCH is just safer as the default is exact match.
INDEX — Return a Value by Position
INDEX takes a data range and returns the value at a specified row and column position within that range.
= INDEX(array,row_num,[col_num])
arrayThe data grid — the range of numbers to return from. Does not need to include the headers or month labels.
row_numWhich row inside the array to return from. Row 1 = first data row, row 5 = fifth data row. This is where MATCH feeds in.
[col_num]Which column inside the array. Optional for a single-column array, required for a multi-column grid. This is where the second MATCH feeds in.
The 2D Lookup — Row × Column
The file has a grid of monthly sales by product. A3 holds a selected month
Data Validation — coming later
and A4 holds a selected product. The task is to return the sales value at their intersection.
months_col, products_row, data_range — verify in your file
Replace these placeholder names with the actual cell references from Match-index.xlsx. The months column is the range containing January–December. The products row is the range containing the product header names. The data range is the numbers grid — without the month labels and without the header row.
Training Exercise
Open Match-index.xlsx. Cell A3 has a Data Validation dropdown for the month, A4 for the product. Write the INDEX + MATCH formula in the yellow result cell. Then test by changing the selections in A3 and A4 — the result should update to match the correct intersection every time.
XLOOKUP can handle 2D lookups using a nested XLOOKUP, but the syntax becomes complex. INDEX + MATCH is the established pattern for grid lookups — it's cleaner, widely understood, and works in all Excel versions. Both approaches are valid; knowing both makes you versatile.
Practice
citysales.xlsx — 3 Questions
Open the file. Row 3 has city names as column headers. Column A has item codes as row labels. Use INDEX + MATCH + MATCH to find the sales for each item–city combination, then type your answer to check.