INDEX and MATCH in Excel: the flexible alternative to VLOOKUP

VLOOKUP is the first lookup function almost everyone learns, and also the one that causes the most headaches: it only looks to the right, it breaks when someone inserts a column and, if you forget an argument, it returns approximate results that look right but are not.

Combining INDEX and MATCH solves those problems and works in every version of Excel. You will see what each function does on its own, how to join them, how to look up by row and column at the same time and which mistakes to watch for.

Why look for an alternative to VLOOKUP

A typical VLOOKUP formula looks like this:

=VLOOKUP(H2,A2:E6,4,FALSE)

It works, but it has some important limitations:

• It only looks to the right. The lookup value must be in the first column of the range. You cannot look up a name and return the code to its left.
• It uses a fixed column number. The 4 means “fourth column”. If someone inserts a column in between, the formula returns a different value without warning.
• Approximate match is the default. If you leave out FALSE, Excel may return a nearby value instead of an error.

The sample data

We will use this product catalog in columns A to E, with headers in row 1:

In cell H2 we will type the code we want to look up, for example P-104.

The two building blocks: INDEX and MATCH

INDEX: returns the value at a position

INDEX takes a range and a row number (and, optionally, a column number) and returns whatever is at that position:

=INDEX(B2:B6,3)

It returns “Monitor”, the third item in the range. With a multi-column range you can also specify the column:

=INDEX(A2:E6,3,4)

It returns 180: row 3 and column 4 of the range, that is, the price of the monitor.

MATCH: finds the position of a value

MATCH does the opposite: it searches for a value in a row or column and tells you its position.

=MATCH("P-103",A2:A6,0)

It returns 3. The last argument is the match type, and it is essential:

• 0: exact match. This is the one you will use almost always.
• 1 (default): the largest value less than or equal to the lookup value. Requires data sorted in ascending order.
• -1: the smallest value greater than or equal to the lookup value. Requires data sorted in descending order.

Combining INDEX and MATCH

The idea is simple: MATCH works out the row and INDEX returns the value from that row. To get the price of the code typed in H2:

=INDEX(D2:D6,MATCH(H2,A2:A6,0))

With P-104 in H2, the result is 650. To build the formula without mistakes:

1. Pick the column that holds the answer (D2:D6, the prices).
2. Pick the column that holds the value you know (A2:A6, the codes).
3. Check that both ranges start and end on the same row.
4. Write MATCH with the final 0 and place it as the second argument of INDEX.

Because the result column is a separate range, inserting columns in the table does not affect the formula: Excel adjusts the references automatically.

Looking up to the left

This is the big advantage over VLOOKUP. If you know the product name (in H3) and want its code, which sits to the left:

=INDEX(A2:A6,MATCH(H3,B2:B6,0))

With “Monitor” in H3, it returns P-103.

Pulling data from another sheet

The ranges can live on different sheets. If you have an order list with the code in column A and the catalog on a sheet called Prices, this formula brings in the price for each order:

=INDEX(Prices!D:D,MATCH(A2,Prices!A:A,0))

If your data arrives spread across several files, for example one catalog per supplier, it is best to bring them together first. Our free tool to merge Excel files joins several workbooks into one, and then you can look things up with a single formula.

Lookups with variable row and column

With two MATCH functions, one for the row and one for the column, you can also choose which value you want to see. Type the name of a column in H4, for example Stock:

=INDEX(A2:E6,MATCH(H2,A2:A6,0),MATCH(H4,A1:E1,0))

With P-104 in H2 and Stock in H4, the result is 8. If you change H4 to Price, you get 650 without touching the formula. It is a very handy way to build a small lookup card: one cell for the code, a drop-down list with the headers and one formula.

Common mistakes and how to avoid them

• #N/A because the value does not exist. This is usually caused by trailing spaces, codes written differently or numbers stored as text (101 is not the same as “101”). To show a message instead of the error, wrap the formula in IFERROR.
• Misaligned ranges. If INDEX uses D1:D6 and MATCH uses A2:A6, the calculated position points to another row and the result is shifted by one, with no warning.
• Forgetting the 0 in MATCH. Without it, Excel uses approximate match and, with unsorted data, may return any row.
• References that move when copied. If you are going to copy the formula down, lock the ranges with $, for example $A$2:$A$6.

Here is the version with a custom message:

=IFERROR(INDEX(D2:D6,MATCH(H2,A2:A6,0)),"Not found")

What about XLOOKUP?

Excel 2021 and Microsoft 365 include XLOOKUP, which does the same job in a single function and looks in any direction:

=XLOOKUP(H2,A2:A6,D2:D6,"Not found")

If you share files with people using Excel 2016 or 2019, INDEX and MATCH remain the safest choice: in those versions, XLOOKUP shows the #NAME? error.

Conclusion

INDEX and MATCH split the job into two tasks: finding the position and returning the value. That split is what makes them so flexible. They look in any direction, tolerate changes to the table’s structure and allow row-and-column lookups with a single formula.

Get into the habit of always typing the 0 in MATCH, checking that the ranges are aligned and cleaning up spaces and formats before looking anything up. Those three habits will prevent most #N/A errors.

Frequently asked questions