The INDEX MATCH formula in Excel is a powerful combination that can search and retrieve data from a specified range based on matching criteria. It's widely used for tasks like looking up values, creating dynamic reports, and analyzing data in large datasets. Combining the INDEX and MATCH functions makes it more flexible and versatile than the more commonly used VLOOKUP function.

This article will cover how INDEX MATCH works, syntax, benefits over VLOOKUP, and some practical examples to help you get the most out of it.

Basics of INDEX MATCHFormula in Excel

INDEX Function

The INDEX function retrieves the value of a specific cell within a range, based on row and column numbers.

Syntax:

=INDEX(array, row_num, [column_num])

array: The range from which you want to retrieve the data.
row_num: The row number within the range.
column_num: (Optional) The column number if the range is more than one column wide.

MATCH Function

The MATCH function finds the relative position of a specified value within a range.

Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value: The value you want to search for.
lookup_array: The range where you want to look for the value.
match_type: 1, 0, or -1. Typically, 0 is used for exact matches.

How INDEX and MATCH Work Together

Using MATCH within INDEX allows you to dynamically locate a cell's row and/or column based on specific criteria. This is particularly useful when the data layout changes because INDEX MATCH is not dependent on the lookup range being the first column (a limitation of VLOOKUP).

Syntax for INDEX MATCH

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

return_range: The range of cells containing the values you want to retrieve.
lookup_value: The value you want to search for in the dataset.
lookup_range: The range where you’re looking for the matching value.

Advantages of INDEX MATCH Over VLOOKUP

Flexibility: Unlike VLOOKUP, which requires the lookup column to be on the left side, INDEX MATCH allows for lookup values in any position.
Performance: INDEX MATCH can be faster with large datasets as it doesn't require sorting.
Dynamic Columns: INDEX MATCH doesn’t break when columns are added or removed, unlike VLOOKUP, which references fixed column numbers.

Also Learn: How to Insert Rupee Symbol in Excel (6 Ways)

Practical Examples of INDEX MATCH Formula in Excel

Example 1: Basic INDEX MATCH Formula in Excel

Suppose you have a list of employee names in column A and their corresponding salaries in column B. You want to find the salary of "John Doe."

A	B
Employee Name	Salary
John Doe	INR 55,000
Jane Smith	INR 60,000
Alice Johnson	INR 75,000

Formula:

=INDEX(B2:B4, MATCH("John Doe", A2:A4, 0))

Explanation: MATCH finds the position of "John Doe" in column A (which is row 1 within the range A2). INDEX then retrieves the corresponding value from B2 (salary).

Basic INDEX MATCH Formula

Example 2: Horizontal Lookup Using INDEX MATCH

INDEX MATCH can also handle horizontal lookups. For example, if you have departments listed across row 1 and their budgets across row 2:

	B	C	D
	HR	Marketing	IT
Budget	INR 8,00,000	INR 5,00,000	INR 3,00,000

To find the budget for the "IT" department, use:

=INDEX(B2:D2, MATCH("IT", B1:D1, 0))

Example 3: Two-Way Lookup Using INDEX MATCH

If you want to look up a value based on both row and column criteria, INDEX MATCH MATCH can handle this.

Suppose you have a matrix of sales data:

	Product A	Product B	Product C
Q1	INR 500	INR 300	INR 450
Q2	INR 600	INR 350	INR 500
Q3	INR 550	INR 320	INR 480

To find the sales of "Product B" in "Q2," use:

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

Using Absolute and Mixed References in INDEX MATCH

When copying an INDEX MATCH formula across cells, it can be useful to fix parts of the formula with absolute or mixed references.

For example:

=INDEX($B$2:$D$4, MATCH("Q2", $A$2:$A$4, 0), MATCH("Product B", B$1:D$1, 0))

In this case:

$B$2:$D$4 and $A$2:$A$4 are fixed with absolute references, ensuring the ranges remain constant.
B$1:D$1 keeps the row fixed but allows the column to adjust if copied horizontally.

Common Errors and How to Fix Them

#N/A Error: Occurs when no match is found. Verify your lookup value exists in the lookup range.
#VALUE Error: Check that all ranges are of the same size and orientation.
Handling Errors: Wrap your formula in an IFERROR to manage these errors gracefully:

=IFERROR(INDEX(B2:B4, MATCH("John Doe", A2:A4, 0)), "Not Found")

Advanced Techniques with INDEX MATCH

Multiple Criteria Lookup

To look up values based on multiple criteria, you can nest INDEX MATCH with an ARRAY formula:

=INDEX(D2:D10, MATCH(1, (B2:B10="Criteria1")*(C2:C10="Criteria2"), 0))

Press Ctrl + Shift + Enter to activate it as an array formula.

We hope you found the above article useful. The INDEX MATCH combination in Excel is a versatile and robust way to perform lookups, allowing for complex, flexible data extraction beyond basic VLOOKUP capabilities. Start incorporating INDEX MATCH in your workbooks to streamline data handling and enhance the precision of your data operations.

Quiz Time!!!

Click here to view quiz

Frequently Asked Questions

Q1. What is the INDEX function in Excel?

The INDEX function returns the value of a cell in a specified row and column within a given range. It’s useful for retrieving data from large datasets efficiently.

Q2. How does the MATCH function work?

The MATCH function searches for a specified item in a range and returns its relative position. It’s often combined with INDEX to find precise data points.

Q3. Why combine INDEX and MATCH?

Combining INDEX and MATCH allows for more flexible lookups compared to VLOOKUP. You can search both vertically and horizontally, making it ideal for complex datasets.

Q4. Can INDEX MATCH work with multiple criteria?

Yes, you can use array formulas or helper columns to enable INDEX MATCH to handle multiple criteria, enhancing your data retrieval capabilities.

Q5. What are some common mistakes with INDEX MATCH?

Common mistakes include incorrect range references, mismatched data types, and forgetting that MATCH defaults to an approximate match unless specified otherwise.

Q6. Is INDEX MATCH faster than VLOOKUP?

Yes, INDEX MATCH is generally faster, especially with large datasets. It avoids the need to search through entire columns, improving efficiency.

Q7. Where can I find practical examples of INDEX MATCH?

You can find practical examples in Excel tutorials, online forums, and our blog post. These resources illustrate real-world applications of the formula.

Articles you shouldn't miss:

Edited by

Shreeya Thakur

Sr. Associate Content Writer at Unstop

I am a biotechnologist-turned-content writer and try to add an element of science in my writings wherever possible. Apart from writing, I like to cook, read and travel.