XXLFormula AI

Excel

INDEX MATCH Combination in Excel

INDEX and MATCH are two functions that work together to look up values in any direction. Unlike VLOOKUP, INDEX/MATCH can search left or right, and it is more flexible and robust.

Syntax

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

Parameters

  • return_rangeRequired

    The range from which to return a result.

  • lookup_valueRequired

    The value to search for.

  • lookup_rangeRequired

    The range where MATCH looks for the value.

Examples

Basic two-way lookup

=INDEX(B2:B100, MATCH(E2, A2:A100, 0))

Finds the value in E2 within column A and returns the corresponding value from column B.

Left-side lookup

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

Looks up a value in column C and returns a result from the left column A.

Common Errors

  • #N/A

    Cause: MATCH could not find the lookup value.

    Fix: Add IFERROR around the formula, or verify the lookup value and range for trailing spaces / type mismatches.

  • #REF!

    Cause: INDEX row or column number is out of range.

    Fix: Ensure the MATCH result is a valid index inside the return_range.

Best Practices

  • Always pass 0 as the third argument to MATCH for exact matches.
  • Use structured references (Excel tables) to make ranges self-expand.
  • Consider migrating to XLOOKUP if you have a modern Excel version.

Frequently Asked Questions

Need a custom version of this formula?

Describe what you need in plain English and get a ready-to-use formula in seconds.

Generate this formula with XLFormula AI