Excel INDEX MATCH Formula Explained Step by Step

If you have been using Excel for any amount of time, you have probably heard of VLOOKUP — one of the most popular Excel formulas for looking up data.

But there is a more powerful, more flexible formula that experienced Excel users swear by:

INDEX MATCH.

Once you understand how INDEX MATCH works, you will find yourself using it in almost every spreadsheet and you will wonder how you ever managed without it.

In this guide, we will break down the Excel INDEX MATCH formula step by step, with clear examples, real-world use cases, and practical tips explained in a beginner-friendly way.

What Is INDEX MATCH in Excel?

INDEX MATCH is not a single formula, it is a combination of two separate Excel functions used together to look up and retrieve data from a table.

• INDEX — Returns the value of a cell at a specific position in a range
• MATCH — Returns the position (row or column number) of a value in a range

When combined, MATCH finds the position of what you are looking for, and INDEX uses that position to return the actual value.

Think of it like this:

Imagine a library. MATCH is the librarian who tells you “the book you want is on shelf 7.” INDEX is you walking to shelf 7 and picking up the book.

Together, they find exactly what you need.

Understanding INDEX on Its Own

Before combining them, let us understand each function individually.

INDEX Syntax

=INDEX(array, row_num, [col_num])

• array — The range of cells you want to return a value from
• row_num — The row number within the array
• col_num — The column number within the array (optional)

INDEX Example

Suppose you have this table:

No	A	B	C
1	Alice	Sales	75000
2	Bob	Marketing	62000
3	Charlie	Engineering	90000
4	Diana	HR	55000

=INDEX(A1:C4, 3, 3)

This returns the value in row 3, column 3 of the range A1:C4 which is 90000 (Charlie’s salary).

INDEX on its own is useful, but you have to manually specify the row and column numbers. That is where MATCH comes in.

Understanding MATCH on Its Own

MATCH Syntax

=MATCH(lookup_value, lookup_array, match_type)

• lookup_value — The value you are searching for
• lookup_array — The range to search in
• match_type — How to match:
  • 0 = Exact match (most commonly used)
  • 1 = Less than (requires sorted data ascending)
  • -1 = Greater than (requires sorted data descending)

MATCH Example

Using the same table above:

=MATCH("Charlie", A1:A4, 0)

This returns 3 because “Charlie” is in the 3rd row of the range A1:A4.

MATCH tells you where something is. INDEX tells you what is there. Together, they are unstoppable.

Combining INDEX and MATCH

Now let us put them together.

Basic INDEX MATCH Syntax

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

Step-by-Step Breakdown

Step 1: Decide what you want to find — the lookup value
Step 2: Decide where to search for it — the lookup range
Step 3: Decide what you want to return — the return range
Step 4: MATCH finds the row position of your lookup value
Step 5: INDEX uses that position to return the value from your return range

Example 1: Basic INDEX MATCH Lookup

Using our employee table:

No	A	B	C
1	Name	Department	Salary
2	Alice	Sales	75000
3	Bob	Marketing	62000
4	Charlie	Engineering	90000
5	Diana	HR	55000

Goal: Find Charlie’s salary.

=INDEX(C2:C5, MATCH("Charlie", A2:A5, 0))

How It Works:

1. MATCH("Charlie", A2:A5, 0) searches for “Charlie” in column A and returns 3 (third row)
2. INDEX(C2:C5, 3) returns the value in the 3rd row of column C which is 90000

Result: 90000

Example 2: Using a Cell Reference Instead of Hardcoding

In real spreadsheets, you rarely hardcode the lookup value. Instead, you reference a cell.

Suppose cell E2 contains the name you want to search for.

=INDEX(C2:C5, MATCH(E2, A2:A5, 0))

Now when you change the value in E2, the formula automatically returns the correct salary. This is how INDEX MATCH is used in real-world dashboards and reports.

Example 3: Looking Up a Value to the Left (Where VLOOKUP Fails)

One of the biggest limitations of VLOOKUP is that it can only look up values to the right of the lookup column. INDEX MATCH has no such restriction. It can look in any direction.

Example: Find the name of the employee who works in Engineering.

=INDEX(A2:A5, MATCH("Engineering", B2:B5, 0))

How It Works:

1. MATCH("Engineering", B2:B5, 0) finds “Engineering” in column B and returns 3
2. INDEX(A2:A5, 3) returns the 3rd value in column A which is Charlie

Result: Charlie

With VLOOKUP, this would be impossible without restructuring your table. With INDEX MATCH, it is effortless.

Example 4: INDEX MATCH with Two Criteria (Advanced)

Sometimes you need to look up a value based on more than one condition. INDEX MATCH handles this beautifully using an array formula.

New Dataset:

No	A	B	C
1	Name	Region	Sales
2	Alice	North	25000
3	Alice	South	30000
4	Bob	North	20000
5	Bob	South	18000

Goal: Find Alice’s sales in the South region.

=INDEX(C2:C5, MATCH(1, (A2:A5="Alice") * (B2:B5="South"), 0))

Important: This is an array formula. In older versions of Excel, press Ctrl + Shift + Enter instead of just Enter. In Excel 365 and Excel 2019+, press Enter normally.

How It Works:

1. (A2:A5="Alice") creates an array: {TRUE, TRUE, FALSE, FALSE} = {1, 1, 0, 0}
2. (B2:B5="South") creates an array: {FALSE, TRUE, FALSE, TRUE} = {0, 1, 0, 1}
3. Multiplying them: {0, 1, 0, 0} — only row 2 satisfies both conditions
4. MATCH(1, {0,1,0,0}, 0) returns 2
5. INDEX(C2:C5, 2) returns 30000

Result: 30000

Example 5: Two-Way INDEX MATCH (Row and Column Lookup)

INDEX MATCH can also look up values using both a row and column condition which is perfect for matrix-style tables.

Dataset:

No	B	C	D
1		Q1	Q2
2	Alice	15000	18000
3	Bob	12000	14000
4	Charlie	19000	21000

Goal: Find Charlie’s Q2 sales.

=INDEX(B2:D4, MATCH("Charlie", A2:A4, 0), MATCH("Q2", B1:D1, 0))

How It Works:

1. First MATCH finds “Charlie” in column A → row 3
2. Second MATCH finds “Q2” in row 1 → column 2
3. INDEX returns the value at row 3, column 2 → 21000

Result: 21000

This is one of the most powerful applications of INDEX MATCH — a true two-dimensional lookup that VLOOKUP simply cannot do.

INDEX MATCH vs VLOOKUP

Feature	VLOOKUP	INDEX MATCH
Look left of lookup column	No	Yes
Two-way lookup	No	Yes
Multiple criteria lookup	No	Yes
Performance on large data	Slower	Faster
Column insertion breaks formula	Yes	No
Beginner friendly	Easier	Slightly harder
Flexibility	Limited	Very flexible
Works with unsorted data	Yes (exact match)	Yes

Why Does Column Insertion Break VLOOKUP?

VLOOKUP uses a hardcoded column number to return results. If you insert a new column into your table, that number shifts and your formula breaks.

INDEX MATCH uses actual ranges instead of column numbers, so inserting columns never breaks your formulas.

Real-World Use Cases of INDEX MATCH

HR and Payroll Systems

Look up employee salaries, job titles, or department details based on employee ID even when the ID column is not the first column in the table.

Inventory Management

Find the price or stock level of a product by searching its product code across a large inventory table.

Financial Dashboards

Pull quarterly revenue figures for a specific department or product line using a two-way lookup across a financial summary table.

Sales Reporting

Look up a salesperson’s performance metrics like total sales, deals closed, region from a master data table based on their name or ID.

Student Grade Systems

Retrieve a student’s grade or score in a specific subject from a matrix table of students and subjects.

When to Use INDEX MATCH vs VLOOKUP

Use VLOOKUP when:

• You are a beginner and the formula is simpler for your needs
• Your lookup column is always the leftmost column
• You are doing a simple one-directional lookup
• You are working with a small, stable table

Use INDEX MATCH when:

• You need to look up values to the left of the search column
• You need a two-way lookup (row and column)
• You need multiple criteria in your lookup
• You are working with large datasets where performance matters
• You want formulas that do not break when columns are inserted or deleted

Advantages and Disadvantages of INDEX MATCH

Advantages

• Works in any direction — left, right, up, or down
• Handles multiple criteria lookups
• Does not break when columns are inserted or deleted
• Faster than VLOOKUP on large datasets
• Supports two-dimensional lookups
• More flexible and scalable than VLOOKUP

Disadvantages

• Slightly more complex syntax for beginners
• Two-criteria lookups require array formula knowledge
• Can be harder to audit and troubleshoot for non-technical users
• Requires understanding of both INDEX and MATCH independently

Common Mistakes to Avoid

• Not using exact match — Always use 0 as the third argument in MATCH for exact matching unless you specifically need approximate matching
• Mismatched range sizes — The lookup range in MATCH and the return range in INDEX must have the same number of rows
• Forgetting Ctrl+Shift+Enter for array formulas — In older Excel versions, multi-criteria INDEX MATCH requires array formula entry
• Using entire columns carelessly — Using A:A instead of A2:A100 works but slows down your spreadsheet significantly on large files
• Hardcoding lookup values — Always reference a cell instead of typing the value directly so your formula updates dynamically
• Confusing row and column order in two-way lookup — Always double-check which MATCH returns the row and which returns the column

INDEX MATCH Cheat Sheet

Task	Formula Pattern
Basic lookup	=INDEX(return_range, MATCH(value, lookup_range, 0))
Left lookup	=INDEX(left_column, MATCH(value, right_column, 0))
Two-way lookup	=INDEX(table, MATCH(row_val, row_range, 0), MATCH(col_val, col_range, 0))
Two criteria	=INDEX(return_range, MATCH(1, (range1=val1)*(range2=val2), 0))
With cell reference	=INDEX(C2:C100, MATCH(E2, A2:A100, 0))

INDEX MATCH is one of those Excel formulas that feels intimidating at first but becomes second nature once you understand the logic behind it.

Here is a simple way to remember it:

• MATCH finds the position — “where is it?”
• INDEX retrieves the value — “what is there?”

Together, they give you a lookup tool that is more powerful, more flexible, and more reliable than VLOOKUP in almost every situation.

Here is a quick recap of what we covered:

• INDEX returns a value at a specific position in a range
• MATCH returns the position of a value in a range
• Combined, they perform flexible lookups in any direction
• INDEX MATCH handles left lookups, two-way lookups, and multiple criteria
• It does not break when columns are inserted or deleted
• It is faster than VLOOKUP on large datasets

Start with the basic formula, practice on your own data, and gradually work your way up to two-criteria and two-way lookups. Before long, INDEX MATCH will be your go-to lookup formula for every Excel project.

FAQs