Tutorials›Excel›INDEX & MATCH
🟢 Free Demo
Excel TutorialTopic 5 of 31

INDEX & MATCH

Master INDEX-MATCH — the professional alternative to VLOOKUP that works in any direction on any column.

✅ What You Will Learn

›How the INDEX function returns a value from any position in a range
›How MATCH finds the position of a value in a row or column
›How combining them creates a flexible two-way lookup
›Why INDEX-MATCH outperforms VLOOKUP on large datasets
›How to do a two-criteria lookup with INDEX-MATCH-MATCH

INDEX-MATCH is a two-function combination that many professionals consider more powerful than VLOOKUP. INDEX returns a value from a table given a row and column number. MATCH returns the position (row or column number) of a value within a range. Together, they form a lookup that works in any direction.

Unlike VLOOKUP, INDEX-MATCH does not require the key column to be on the left. It can look up a value and return a result from a column to the left of the key. It also recalculates faster on very large tables because MATCH only searches one column instead of scanning the entire table.

Once you understand INDEX and MATCH individually, combining them feels natural. Start with MATCH to find the row number, then feed that into INDEX to get the value.

📋 Regional sales data — two-way lookup candidate

ProductNorthSouthEastWest
Laptop420000310000280000195000
Mobile185000420000390000270000
Tablet95000140000180000120000
Accessories62000880007400055000

Syntax

EXCEL SYNTAX
=INDEX(return_range, row_num, [col_num])
  → returns the value at the specified position

=MATCH(lookup_value, lookup_array, [match_type])
  match_type: 0 = exact match (use this for data lookups)

Combined:
=INDEX(return_range, MATCH(lookup_value, lookup_array, 0))

Two-way lookup (row and column both dynamic):
=INDEX(table, MATCH(row_key, row_labels, 0), MATCH(col_key, col_labels, 0))

Examples

Example 1MATCH — find what row a product is in
=MATCH("Mobile", A2:A5, 0)
OUTPUT
2
💡

MATCH returns the position within the range — Mobile is the 2nd item in A2:A5. The 0 means exact match.

Example 2INDEX-MATCH — look up South sales for Mobile
=INDEX(B2:E5, MATCH("Mobile", A2:A5, 0), MATCH("South", B1:E1, 0))
OUTPUT
420000
💡

The first MATCH finds the row (Mobile = row 2). The second MATCH finds the column (South = column 2). INDEX returns the value at that intersection.

Example 3INDEX-MATCH looking LEFT — return product name from a price
=INDEX(A2:A5, MATCH(95000, C2:C5, 0))
OUTPUT
Tablet
💡

VLOOKUP cannot look left — it always returns columns to the right of the key. INDEX-MATCH has no such restriction.

📌 Key Points to Remember

  • ✓INDEX(range, row, col) — if range is one column, col argument can be omitted
  • ✓MATCH returns a position (1, 2, 3…) not a value — it is the row or column number for INDEX
  • ✓Always use 0 as the third argument of MATCH for exact lookups
  • ✓INDEX-MATCH is not affected by inserting or deleting columns — VLOOKUP breaks when columns shift
  • ✓For the largest datasets, INDEX-MATCH is noticeably faster than VLOOKUP

🏢 Real-World Application

Supply chain analysts use two-way INDEX-MATCH to look up delivery lead times from a matrix table where rows are suppliers and columns are product categories. Instead of maintaining separate lookup columns, one formula handles any combination. Finance teams use it to extract budget figures from multi-dimensional planning models where the key can appear in any direction.

⚠️ Common Mistakes to Avoid

WRONGUsing MATCH with match_type 1 or -1 for text data
FIXAlways use 0 for exact matches. match_type 1 and -1 are for sorted numerical ranges and give wrong results on unsorted or text data.
WRONGINDEX range and MATCH range covering different numbers of rows
FIXThe return range in INDEX must have the same number of rows as the lookup range in MATCH. If MATCH searches A2:A100, INDEX must return from a range with exactly 99 rows.
WRONGWrapping INDEX-MATCH in IFERROR before confirming it works
FIXAlways test the formula without IFERROR first so error messages help you debug. Add IFERROR only after the formula returns correct results.
✏️Test Yourself

What does =MATCH("East", {"North","South","East","West"}, 0) return?

❓ Frequently Asked Questions

When should I use INDEX-MATCH instead of VLOOKUP?

Use INDEX-MATCH when: (1) your return column is to the left of the key, (2) columns may be inserted/deleted, (3) you need a two-way lookup, or (4) you are working with very large tables where speed matters.

Can INDEX-MATCH replace all VLOOKUP uses?

Yes — everything VLOOKUP does, INDEX-MATCH can do, and more. VLOOKUP is simpler to write for straightforward right-column lookups, so both have their place.

What is the difference between INDEX-MATCH and XLOOKUP?

XLOOKUP (Microsoft 365 only) is easier to write than INDEX-MATCH for most lookups. INDEX-MATCH remains useful for two-way lookups and on older Excel versions.

✏️ Practice Exercise

Build a 5×5 sales matrix with product names as row headers and months (Jan–May) as column headers. Use a two-way INDEX-MATCH formula that reads a product name from one cell and a month from another cell, and returns the correct sales figure from the matrix dynamically.

← PreviousVLOOKUP & XLOOKUPNext →SUMIF & COUNTIF
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.