INDEX and MATCH in Excel: Two-Dimensional Lookups

VLOOKUP finds a value in a column. HLOOKUP finds a value in a row. Neither can do both at once, and both require you to hardcode a column or row number that breaks when the table changes.

INDEX and MATCH don't have these problems. Used together, they find the exact intersection of a row and column in a table — and adapt automatically when columns are added or reordered.

The MATCH Function

MATCH returns the position of a value in a range — not the value itself, just where it sits.

=MATCH(lookup_value, lookup_array, [match_type])

Parameter	Required	Description
lookup_value	Yes	The value you're searching for.
lookup_array	Yes	The row or column range to search. Must be one-dimensional.
match_type	No	0 for exact match. 1 (default) finds the largest value ≤ lookup_value. -1 finds the smallest value ≥ lookup_value.

=MATCH("Brazil", B4:H4, 0)

// Returns 5 if "Brazil" is the fifth item in the range
// The result is a position number, not the value itself

The INDEX Function

INDEX returns the value at a given position in a range.

=INDEX(reference, row_num, column_num)

Parameter	Required	Description
reference	Yes	The range to return a value from.
row_num	Yes	Which row in the range to return. 0 returns the entire column.
column_num	No	Which column in the range to return. 0 returns the entire row.

=INDEX(B5:H16, 6, 5)

// Returns the value in row 6, column 5 of the range B5:H16
// Hard-coding row and column numbers isn't useful — this is where MATCH comes in

INDEX + MATCH: Two-Dimensional Lookup

The power of this combination is that MATCH supplies the row and column numbers that INDEX needs — dynamically, based on values in the sheet.

Given a table with months as row headers and countries as column headers:

=INDEX(B5:H16, MATCH(B20, A5:A16, 0), MATCH(B19, B4:H4, 0))

// B5:H16       — the data range (values only, no headers)
// MATCH(B20, A5:A16, 0) — finds which row matches the month in B20
// MATCH(B19, B4:H4, 0)  — finds which column matches the country in B19

If B20 contains "June" and B19 contains "Japan", this returns Japan's June sales figure. Change either cell and the result updates instantly.

Why INDEX + MATCH Beats VLOOKUP

VLOOKUP requires a hardcoded column number as its third argument:

=VLOOKUP(B20, A4:H16, 5, FALSE)   // column 5 — breaks if columns are inserted

If anyone inserts a column in the table, the number is wrong and the formula returns the wrong data without any error. INDEX + MATCH doesn't have this problem — it finds the column by name, not by position.

VLOOKUP also can't look left. The lookup column must be the leftmost column in the range. INDEX + MATCH has no such restriction — the reference range and the lookup ranges are independent.

Combining MATCH with VLOOKUP

If you're not ready to replace VLOOKUP entirely, MATCH can at least fix the hardcoded column number problem:

=VLOOKUP(B20, A4:H16, MATCH(B19, B4:H4, 0) + 1, FALSE)

// MATCH(B19, B4:H4, 0)  — finds the column position of the country in B19
// +1                     — adjusts because VLOOKUP's range includes the row-label column

This is more resilient than a hardcoded number, but INDEX + MATCH is cleaner — there's no offset adjustment needed and no restriction on lookup direction.

Notes & Gotchas

• MATCH returns a #N/A error when the value isn't found. Wrap with IFERROR or IFNA if the lookup value might not exist in the table.
• The data range in INDEX should not include headers — just the values. The lookup arrays in MATCH should include headers (or whichever row/column holds the labels you're matching against).
• Both MATCH lookup arrays must be the same orientation as their respective headers: a row range for column lookups, a column range for row lookups.
• MATCH is case-insensitive. "japan" and "JAPAN" return the same position.