What is the most basic Excel INDEX MATCH formula?

The most basic formula is =INDEX(return_range, MATCH(lookup_value, key_range, 0)). MATCH finds the position, and INDEX returns the value at that position.

What is the most common reason for #N/A in INDEX MATCH?

The most common reason is that the lookup value is not in the key range, or that spaces or data-type mismatches between numbers and text make values that appear identical different. Use TRIM/CLEAN and standardize data types to resolve it.

How do you perform a two-way (row and column) lookup with INDEX MATCH?

Find the row key and column key with separate MATCH functions, then use them as the row_num and column_num arguments in INDEX. For example: =INDEX(data, MATCH(row_key, row_headers, 0), MATCH(column_key, column_headers, 0))

Can INDEX MATCH perform multiple-criteria lookups, such as branch and product?

Yes. A common pattern is =INDEX(return_column, MATCH(1, (criteria_range1=value1)*(criteria_range2=value2), 0)). Older versions of Excel may require array formula entry.

What should I do to prevent formulas from breaking when columns are added?

Convert ranges to tables and specify INDEX's return range directly instead of using VLOOKUP's column number. Also, it is safest to start with 0 for an exact match in MATCH.

CONTENTS

How to Use Excel INDEX MATCH Properly: A Complete Practical Guide

To use Excel INDEX MATCH effectively, you need to build a reliable structure (tables, references, and exact matches) before memorizing the syntax. VLOOKUP is convenient, but if you have experienced reports breaking after inserting or deleting a column, the INDEX+MATCH combination is much more reliable.

Quick Fix: Get Your First Excel INDEX MATCH Result in 3 Minutes

Choose the column to return (result range) first.
Identify the value to find (key).
Use MATCH to find its position: =MATCH(lookup_value, key_range, 0)
Use INDEX to return the value at that position: =INDEX(return_range, MATCH(lookup_value, key_range, 0))

Why INDEX MATCH Is Less Likely to Break in Real-World Workbooks

Unlike VLOOKUP, you do not need to count column numbers, making it safer when columns are inserted or deleted.
It supports left-side lookups.
It handles two-way (row and column) lookups cleanly.
It is easy to extend to advanced lookups, such as multiple criteria and latest-value lookups.

Prepare the Practical Example: Sample Data

PriceList (Master)

ItemCode	ItemName	Category	Price
T001	T-shirt	Basic	19000
T002	Polo	Plus	25000
B014	Pants	Basic	35000
O010	Jacket	Pro	159000
S201	Shoes	Pro	140000

Orders

OrderID	ItemCode	Qty
O-1001	T002	3
O-1002	S201	1
O-1003	T001	2

Recommended setup: Select the range → Insert > Table → select “My table has headers” → name the tables PriceList and Orders.

Practical Example 1: Look Up Prices with an Exact Match (0)

Retrieve Price from PriceList using the ItemCode in Orders:

=INDEX(PriceList[Price], MATCH([@ItemCode], PriceList[ItemCode], 0))

Amount:

=[@Qty] * [@UnitPrice]

Practical Example 3: Two-Way (Row and Column) Lookup

Find the row key (ItemCode) and column key (month header) with separate MATCH functions, then pass them to INDEX.

=INDEX(MonthlySales,
  MATCH(B2, MonthlySales[ItemCode], 0),
  MATCH(C2, MonthlySales[#Headers], 0)
)

Practical Example 4: Multiple-Criteria Lookup

=INDEX(Sales[Price],
  MATCH(1, (Sales[Branch]=E2)*(Sales[ItemCode]=F2), 0)
)

Troubleshooting

Symptom	Likely Cause	Solution
#N/A	The key does not exist, or spaces/data types do not match	Use TRIM/CLEAN, standardize numbers and text, and verify that the key exists.
Incorrect value (approximate match)	MATCH uses 1/-1, but the data is not sorted	Check the sorting requirement and use 0 whenever possible.
#VALUE!	Array formula entry or compatibility issue	In older versions, check whether Ctrl+Shift+Enter is required.

External References (Official Documentation)

Microsoft 프로그램 무료 온라인 사용법: Word·Excel·PowerPoint·OneNote·Outlook·Teams를 “제대로” 쓰는 최적 루트 (2026)

How to Use Excel INDEX MATCH: Build Lookup Formulas That Don’t Break