INDEX MATCH Guide: The Powerful Alternative to VLOOKUP

Why INDEX MATCH?

VLOOKUP is the most well-known lookup function in spreadsheets, but it has significant limitations. INDEX MATCH is a two-function combination that solves every one of those limitations while being just as fast -- often faster -- on large datasets.

Here is why experienced spreadsheet users reach for INDEX MATCH instead of VLOOKUP:

• Look up in any direction. VLOOKUP can only search the leftmost column and return a value to the right. INDEX MATCH can look left, right, or in any column arrangement.
• Columns can be inserted or deleted. VLOOKUP uses a hard-coded column number. If you insert a column in your data, every VLOOKUP breaks. INDEX MATCH references columns by address, so structural changes do not break it.
• Better performance. On large datasets, VLOOKUP scans the entire range for each lookup. INDEX MATCH with exact match can be more efficient because MATCH only searches one column.
• Multiple criteria lookups. INDEX MATCH naturally extends to multi-criteria lookups using array formulas, which VLOOKUP cannot do without helper columns.
• Two-way lookups. By using MATCH for both row and column, INDEX can retrieve a value at any intersection in a table.

The trade-off is that INDEX MATCH has a slightly steeper learning curve. Once you understand how the two functions work individually, the combination becomes intuitive.

Understanding INDEX

The INDEX function returns the value at a specific position (row and/or column) within a range. Think of it as "go to row N of this range and give me the value."

=INDEX(array, row_num, [column_num])

• array — The range of cells to retrieve from.
• row_num — The row position within the range (1 = first row of the range).
• column_num — Optional. The column position within the range. Required when the array spans multiple columns.

INDEX Examples

If cells B2:B10 contain product names, return the 5th product:

=INDEX(B2:B10, 5)

From a two-dimensional range A1:C10, return the value in row 3, column 2:

=INDEX(A1:C10, 3, 2)

On its own, INDEX is limited because you need to know the exact row number. That is where MATCH comes in.

Understanding MATCH

The MATCH function searches for a value in a single row or column and returns its position number. Think of it as "find this value and tell me what row number it is in."

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value — The value to search for.
• lookup_array — The single row or column to search within.
• match_type — Controls matching behavior: 0 for exact match, 1 for largest value less than or equal (requires sorted data), -1 for smallest value greater than or equal.

MATCH Examples

Find the position of "Widget" in column A:

=MATCH("Widget", A2:A100, 0)

If "Widget" is in cell A7, the formula returns 6 (the 6th row within the range A2:A100, since A2 is row 1). The 0 specifies exact match, which is what you want in almost all lookup scenarios.

If the lookup value is not found, MATCH returns a #N/A error. Wrap the formula in IFERROR or IFNA to handle this gracefully.

Combining INDEX and MATCH

The core pattern is simple: MATCH finds the row position, and INDEX retrieves the value from that row in a different column. The general formula is:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Breaking this down step by step:

1. MATCH(lookup_value, lookup_range, 0) searches for the value and returns its row position.
2. INDEX(return_range, ...) takes that position number and retrieves the corresponding value from the return column.

For example, look up the price of a product. Column A has product names, column C has prices, and you want the price for the product name in E1:

=INDEX(C2:C100, MATCH(E1, A2:A100, 0))

The key detail: the lookup range and the return range must have the same number of rows, and they must start at the same row. If your lookup range is A2:A100, your return range should also start at row 2 (e.g., C2:C100).

INDEX MATCH Examples

1. Basic Lookup (Equivalent to VLOOKUP)

Look up an employee's department. Column A has employee IDs, column B has names, column C has departments. Find the department for the employee ID in cell F1:

=INDEX(C2:C500, MATCH(F1, A2:A500, 0))

This is functionally identical to =VLOOKUP(F1, A2:C500, 3, FALSE) but will not break if columns are inserted or rearranged.

2. Left Lookup (Impossible with VLOOKUP)

This is where INDEX MATCH truly shines. Suppose column C has product codes and column A has product descriptions. You want to look up a product code and return the description to its left:

=INDEX(A2:A500, MATCH(F1, C2:C500, 0))

VLOOKUP cannot do this because it only returns values from columns to the right of the search column. INDEX MATCH does not care about column order -- the lookup range and return range are completely independent.

3. Two-Way Lookup (Row and Column Match)

Retrieve a value at the intersection of a specific row and column. Imagine a table where row headers (A2:A20) are product names, column headers (B1:F1) are months, and the body contains sales figures. Find the sales for a specific product and month:

=INDEX(B2:F20, MATCH(H1, A2:A20, 0), MATCH(H2, B1:F1, 0))

Here two MATCH functions are at work:

• The first MATCH finds the row position by looking up the product name (H1) in the row headers (A2:A20).
• The second MATCH finds the column position by looking up the month (H2) in the column headers (B1:F1).
• INDEX then returns the value at that exact row-column intersection within the data range B2:F20.

4. Multiple Criteria Lookup

Look up a value based on two or more conditions. For example, find the sales amount where the region (column A) is "West" and the product (column B) is "Widget". Column D has sales amounts:

=INDEX(D2:D500, MATCH(1, (A2:A500="West")*(B2:B500="Widget"), 0))

This is an array formula. In Excel, enter it with Ctrl+Shift+Enter (or just Enter in Microsoft 365 and Google Sheets). The multiplication (condition1)*(condition2) creates an array of 1s and 0s, and MATCH finds the first 1 (meaning both conditions are met).

This technique is one of the most powerful features of INDEX MATCH. VLOOKUP has no built-in way to search on multiple criteria without creating a helper column.

INDEX MATCH vs VLOOKUP vs XLOOKUP

Here is a side-by-side comparison of the three main lookup approaches in spreadsheets:

When to Use Each

Use VLOOKUP when: you are doing a quick, simple lookup where the search column is on the left, you do not expect columns to change, and readability matters most. VLOOKUP is perfectly fine for simple dashboards, one-off reports, and situations where everyone on the team knows VLOOKUP.

Use INDEX MATCH when: you need to look up to the left, your spreadsheet structure may change, you need two-way lookups, you have multiple criteria, or you are building a template that needs to be robust. INDEX MATCH is the professional-grade choice and works in every version of Excel, Google Sheets, and Apple Numbers.

Use XLOOKUP when: you are working exclusively in Excel 2021+ or Microsoft 365 (or Google Sheets), you want a cleaner syntax than INDEX MATCH, and you do not need two-way or multi-criteria lookups. XLOOKUP is the modern replacement for VLOOKUP with built-in error handling and the ability to look in any direction.

Bottom line: If you are unsure which to learn, invest in INDEX MATCH. It works everywhere, handles every scenario, and the skills transfer to any spreadsheet platform. XLOOKUP is a nice convenience when available but is not yet universally supported.

Related Guides

• VLOOKUP Guide — The classic lookup function and when it is still the right choice.
• XLOOKUP Guide — The modern Excel lookup function with built-in error handling.
• SUMIF & SUMIFS Guide — Sum values that meet one or more conditions.
• FAQ — Frequently asked questions about FormulaAI.