How to Use INDEX and MATCH Instead of VLOOKUP

INDEX and MATCH work as a pair: MATCH finds which row your lookup value sits in, and INDEX returns the value from that row in any column you choose. Because you point at the return column directly, you can look up to the left, and your formula won't break when someone inserts a column.

=INDEX($C$2:$C$8, MATCH(F2, $A$2:$A$8, 0))

• $C$2:$C$8 is the return range: the column that holds the answer you want.
• F2 is the lookup value: what you are searching for.
• $A$2:$A$8 is the lookup range: the column to search in.
• 0 means exact match.

This works in every version of Excel and in Google Sheets. If your regional settings use semicolons as argument separators (common in parts of Europe and Latin America), replace each comma with a semicolon.

Example: look up a price by product ID

Here is a small product list in A1:D7. Row 1 holds the headers.

Type 1004 in F2 and put this in G2:

=INDEX($C$2:$C$7, MATCH(F2, $A$2:$A$7, 0))

Expected result: 39.00. ID 1004 is the 4th item in A2:A7, so INDEX returns the 4th item in C2:C7.

Now the lookup VLOOKUP can't do directly. Type Stapler in F3 and find its ID, which sits to the left of the product name:

=INDEX($A$2:$A$7, MATCH(F3, $B$2:$B$7, 0))

Expected result: 1003. "Stapler" is the 3rd item in B2:B7, so INDEX returns the 3rd item in A2:A7.

How it works: MATCH, then INDEX

The easiest way to understand the formula is to build it in two pieces. A position here just means "which item in the list, counting from 1."

Step 1: Test MATCH on its own

=MATCH(1004, $A$2:$A$7, 0)

This returns 4, because 1004 is the fourth item in the range. The syntax is MATCH(lookup_value, lookup_array, [match_type]). The 0 tells Excel to find an exact match. Microsoft documents the match types on its MATCH function support page, including 1 (the default, largest value less than or equal to your lookup value) and -1.

That default is why the 0 matters. If you leave it off, MATCH assumes your list is sorted ascending and may return a wrong row without any warning. With text IDs or unsorted data, that produces answers that look plausible but are incorrect.

Step 2: Wrap it in INDEX

=INDEX($C$2:$C$7, 4)

INDEX(array, row_num) returns the item at that row position within the range. Here it gives 39.00. Replace the hard-coded 4 with the MATCH from step 1 and you have the full formula.

Both ranges must be the same size and start on the same row. If the lookup range is A2:A7 but the return range is C3:C8, position 4 in one is a different record than position 4 in the other. Excel won't complain; you'll just get a neighbor's value.

Step 3: Lock the ranges with $

Dollar signs make references absolute, so they don't shift when you copy the formula down. Notice that F2 has no dollar signs, since you want it to move to F3, F4 and so on. The two ranges should be locked.

Convert a VLOOKUP you already have

Take this typical formula:

=VLOOKUP(F2, A:C, 3, FALSE)

The INDEX/MATCH version is:

=INDEX(C:C, MATCH(F2, A:A, 0))

Here is how each piece maps over.

The column index number is the fragile part of VLOOKUP. If someone inserts a column between A and C, the 3 now points at the wrong column, and the formula quietly returns different data. In INDEX/MATCH, Excel and Google Sheets adjust the C:C reference when columns move, so the formula keeps pointing at the right column.

Whole-column references like A:A are fine for small and medium sheets. For tidier formulas, or if you plan to share the file, bounded ranges such as $A$2:$A$500 are easier to audit.

Variations you will actually use

Lookup to the left

You already saw this one: the return range can be anywhere, including left of the lookup column. VLOOKUP only returns columns to the right of the one it searches.

Two-way lookup (row and column)

To pick both the row and the column dynamically, use a second MATCH on the header row. INDEX accepts a row number and a column number:

=INDEX($B$2:$D$7, MATCH(F2, $A$2:$A$7, 0), MATCH(G1, $B$1:$D$1, 0))

With 1005 in F2 and Stock in G1, the first MATCH returns 5 (the fifth ID) and the second returns 3 (Stock is the third header in B1:D1). Expected result: 6, the Whiteboard's stock.

Notice that the data block (B2:D7) and the header row (B1:D1) cover the same columns. If those don't line up, the column number will be off.

Handle not-found results

If the lookup value isn't in the list, MATCH returns #N/A. Wrap the formula to show something friendlier:

=IFNA(INDEX($C$2:$C$7, MATCH(F2, $A$2:$A$7, 0)), "Not found")

IFNA catches only #N/A, which makes it the safer choice. IFERROR hides every error, including #REF! and #VALUE!, which can mask a broken range. IFNA is available in Excel 2013 and later and in Google Sheets; in older versions, use IFERROR.

Approximate match for tiers

For banded values such as commission rates, sort the thresholds ascending and use match type 1:

With the thresholds in A2:A4 and rates in B2:B4, this looks up sales of 18,500:

=INDEX($B$2:$B$4, MATCH(18500, $A$2:$A$4, 1))

MATCH finds the largest threshold that is not above 18,500, which is 10,000 (position 2), so the result is 4%. These numbers are only an illustration of the mechanics. For real commission, payroll or tax tables, confirm the figures with a qualified professional or an official source such as the IRS.

Multiple criteria

Matching on two columns at once (say, customer and month) needs either an array-style formula or a helper column that joins the two fields, such as =A2&"|"&B2, which you then look up with the usual pattern. The helper column is the simplest option for most people, and it keeps the formula readable.

Common errors and fixes

A quick debugging habit: evaluate MATCH by itself in a spare cell. If it returns a sensible number, the problem is in the INDEX range. If it returns #N/A, the problem is the lookup value or lookup range.

INDEX MATCH vs VLOOKUP vs XLOOKUP: which should you use?

There isn't one winner. Pick based on your task and who will open the file.

XLOOKUP needs Microsoft 365 or Excel 2021 or later. The equivalent of our first example is:

=XLOOKUP(F2, $A$2:$A$7, $C$2:$C$7)

It defaults to exact match, so there's no 0 to forget. Microsoft's MATCH page also notes that XMATCH, the newer function, returns exact matches by default. If you work only in modern Excel, XLOOKUP is easier to read. INDEX/MATCH remains the dependable choice when compatibility matters, and it is worth knowing because you will run into it in other people's workbooks.

Some jobs call for a different tool entirely:

• Power Query is better for merging two large tables or repeating the same combination every month.
• A pivot table is better when you want totals or counts by category, not a single looked-up value.
• FILTER (Microsoft 365 and Excel 2021 or later) returns every matching row when there are duplicates.

Excel vs Google Sheets notes

INDEX and MATCH behave the same in Google Sheets, and the formulas in this article work unchanged. XLOOKUP is also available in Sheets, though you should check the current function list in your own account before building a shared template around it. Whole-column references work in both applications, though bounded ranges are easier to check. In both, some regional settings require semicolons instead of commas.

For more on the MATCH and INDEX approach, the Excel Campus guide and the Ablebits walkthrough both give extra examples.

Quick checklist

• Write MATCH alone first and confirm it returns a sensible position.
• End every MATCH with , 0 unless you truly want a sorted approximate lookup.
• Make the return range and lookup range the same size and start row.
• Lock both ranges with $ before copying down.
• Use IFNA instead of IFERROR so real mistakes stay visible.
• Check for stray spaces and text-formatted numbers when #N/A appears unexpectedly.