fx
FunctionPedia
INDEXMATCHVLOOKUPLookupAdvanced

Master INDEX/MATCH for Two-Way Lookups

Learn how to use INDEX and MATCH together to create powerful two-way lookups that work in any direction, unlike VLOOKUP.

Introduction

While VLOOKUP is the most well-known lookup function, it has significant limitations—it can only look to the right and breaks when columns are inserted. INDEX/MATCH is the superior alternative that solves these problems and enables two-way lookups where you can search both rows and columns.

Prerequisites

  • Basic understanding of cell references
  • Familiarity with VLOOKUP (helpful but not required)
  • Excel 2007 or later, or Google Sheets

1Understanding INDEX and MATCH Separately

Before combining them, let's understand each function individually. INDEX returns a value from a specific position in a range, while MATCH finds the position of a value within a range.

1

INDEX function

INDEX takes a range and returns the value at a specified row and column intersection.

=INDEX(range, row_num, [column_num])
2

MATCH function

MATCH searches for a value in a range and returns its relative position (1-based).

=MATCH(lookup_value, lookup_array, [match_type])

MATCH is 1-based, meaning the first item is position 1, not 0.

Use match_type 0 for exact matches (most common).

2Basic INDEX/MATCH Combination

The magic happens when you nest MATCH inside INDEX. MATCH finds the row number, and INDEX retrieves the value from that row.

Example

=INDEX(B2:B10, MATCH(A12, A2:A10, 0))
Result: John Smith

MATCH finds the position of the lookup value in column A, then INDEX returns the corresponding value from column B.

This works like VLOOKUP but without the column index limitation.

The range can be in any direction—left, right, up, or down.

3Two-Way Lookup with INDEX/MATCH/MATCH

For a true two-way lookup, use two MATCH functions—one for the row and one for the column.

Example

=INDEX(B2:E10, MATCH(A12, A2:A10, 0), MATCH(B12, B1:E1, 0))
Result: $75,000

The first MATCH finds the row for the employee name, the second MATCH finds the column for the department, and INDEX returns the salary at their intersection.

Lock your range references with $ for copying formulas.

This is far more flexible than VLOOKUP for dynamic data.

4INDEX/MATCH vs VLOOKUP: Key Differences

Let's compare INDEX/MATCH with VLOOKUP to understand why you should make the switch.

1

Lookup Direction

VLOOKUP can only search left-to-right. INDEX/MATCH can search in any direction.

2

Column Safety

VLOOKUP uses column numbers that break when columns are inserted. INDEX/MATCH uses actual column references.

3

Flexibility

INDEX/MATCH supports two-way lookups and more complex scenarios.

Functions Used

INDEX

Returns the value at a specified position in a range

MATCH

Searches for a value in a range and returns its position

VLOOKUP

Searches for a value in the first column and returns a value from another column

Summary

INDEX/MATCH is the modern alternative to VLOOKUP that provides greater flexibility and reliability. By combining these two functions, you can create powerful lookups that work in any direction and survive column insertions. The two-way lookup technique using INDEX/MATCH/MATCH is essential for advanced spreadsheet work.

Next Steps

  • Practice creating basic INDEX/MATCH lookups
  • Try a two-way lookup with your own data
  • Learn about XLOOKUP as another alternative
  • Explore how to combine INDEX/MATCH with IFERROR for error handling