INDEX & MATCH

Before XLOOKUP existed, experienced Excel users preferred a pair of functions over VLOOKUP: INDEX and MATCH. Together they can look left, survive inserted columns, and look up across rows and columns at the same time. They work in every version of Excel and in Google Sheets, so you will find them in many professional workbooks.

In this lesson you will learn what each function does on its own, combine them into a lookup, build a two-way lookup, and meet the modern XMATCH.

The sample sheet

A bakery chain in Jaipur records monthly sales (₹) for its four branches:

INDEX: get the value at a position

=INDEX(array, row_num, [column_num])

INDEX returns the value at a given row and column inside a range. Positions count from the top-left of the range, not from the sheet:

=INDEX(B2:E5, 3, 2)

129900

Row 3 of B2:E5 is Vaishali Nagar, and column 2 is May. For a single column, you only need the row number: =INDEX(A2:A5, 4) returns Mansarovar.

MATCH: find the position of a value

=MATCH(lookup_value, lookup_array, [match_type])

MATCH does the opposite job: it tells you where a value is, as a number.

=MATCH("Vaishali Nagar", A2:A5, 0)

3

The 0 means exact match. Like VLOOKUP's last argument, it matters:

Leaving out the third argument gives match type 1, which returns wrong positions on unsorted lists without any error. Always type the 0 for exact lookups.

INDEX and MATCH together

Now combine them. MATCH finds the row; INDEX fetches the value from that row. With a branch name in G2:

=INDEX(D2:D5, MATCH(G2, A2:A5, 0))

When G2 is Malviya Nagar, MATCH returns 2, and INDEX returns the 2nd value of the June column: 171900.

Because the lookup range and the return range are separate, the return column can be on either side of the lookup column, and inserting a column does not break the formula. That is exactly what VLOOKUP cannot do.

Two-way lookups

The real strength of this pair is looking up a row and a column at the same time. Put a branch in G2 and a month in G3, then let one MATCH find the row and another find the column:

=INDEX(B2:E5, MATCH(G2, A2:A5, 0), MATCH(G3, B1:E1, 0))

171900

With Malviya Nagar in G2 and Jun in G3, the first MATCH gives row 2 and the second gives column 3. Change either cell and the answer updates. This pattern suits any grid: courier charges by zone and weight, fees by course and year, or train fares by class and distance band.

Finding the label of the best value

MATCH can find where the maximum is, and INDEX can return its label. Which month was best for C-Scheme?

=INDEX(B1:E1, MATCH(MAX(B2:E2), B2:E2, 0))

Jul

MAX(B2:E2) is 201300; MATCH finds it in position 4; INDEX returns the 4th heading. The same idea down a column finds the best branch in a month: =INDEX(A2:A5, MATCH(MAX(E2:E5), E2:E5, 0)) returns C-Scheme for July.

Advanced: a whole column with INDEX

If you give INDEX a row number of 0, it returns the entire column. Wrap it in SUM to total whichever month is named in G3:

=SUM(INDEX(B2:E5, 0, MATCH(G3, B1:E1, 0)))

601550

That is the June total for all four branches. A column number of 0 works the same way for a whole row.

XMATCH: the modern MATCH

XMATCH (Microsoft 365, Excel 2021 and later) works like MATCH but defaults to an exact match, so you cannot forget the 0:

=XMATCH(G3, B1:E1)

3

It also has the same extra modes as XLOOKUP: -1 for exact or next smaller, 1 for exact or next larger, 2 for wildcards, and a search mode to search from the end. In newer versions you can replace MATCH(..., 0) with XMATCH(...) in any formula on this page.

Common mistakes

• Forgetting the 0 in MATCH. The default approximate match returns wrong positions on unsorted data.
• Ranges that do not line up. INDEX(D2:D5, MATCH(G2, A3:A6, 0)) is off by one row. The lookup range and the return range must start on the same row.
• Counting positions from the sheet. Positions are counted inside the range: row 1 of B2:E5 is sheet row 2.
• Including the heading in one range but not the other. Either include headings in both ranges or in neither.

Key points

• INDEX(range, row, column) returns the value at a position inside a range.
• MATCH(value, range, 0) returns the position of a value; always use 0 for exact matches.
• INDEX(return_range, MATCH(value, lookup_range, 0)) looks up in any direction and survives inserted columns.
• Two MATCH functions inside INDEX give a two-way lookup across rows and columns.
• XMATCH defaults to exact match and works in Microsoft 365 and Excel 2021 or later.

=INDEX(B2:B5, MATCH(G2, A2:A5, 0))
=INDEX(A2:A5, MATCH(MIN(E2:E5), E2:E5, 0))
=INDEX(B1:E1, MATCH(MIN(B5:E5), B5:E5, 0))
=SUM(INDEX(B2:E5, MATCH(G2, A2:A5, 0), 0))

154800   (with Malviya Nagar in G2)
Mansarovar
Apr
655300   (with Malviya Nagar in G2)