Download Leila’s INDEX & MATCH Template
Solve complex lookups where VLOOKUP fails. Download the exact workbook from the video to master the “GPS & Map” logic.
Why is INDEX MATCH better than VLOOKUP?
VLOOKUP is one of the most popular lookup functions in Excel. But VLOOKUP has many limitations. It can only look to the right from the first column. It only finds the first match in a data set.
Is XLOOKUP better than INDEX MATCH?
While INDEX and MATCH has long been the “Gold Standard” for flexible lookups, Microsoft introduced a new powerhouse: XLOOKUP.
If you are using Microsoft 365 or Office 2021 (or later), XLOOKUP combines the power of INDEX and MATCH into a single, easy-to-read formula. It handles left-lookups, horizontal searches, and error handling automatically. Without the need to nest two different functions.
Why you might still use INDEX and MATCH:
- Backwards Compatibility: It works in all versions of Excel (essential if you share files with people on older versions).
- Complex Matrix Lookups: It is still slightly more intuitive for certain advanced 2-way matrix lookups.
Why you should switch to XLOOKUP:
- Simpler Syntax: No more confusing commas and nested brackets.
- Robustness: You don’t have to remember to use “0” for an exact match; it’s the default!
- Reverse Search: Easily find the last occurrence in a list instead of the first.
Ready to see the modern way to lookup data? Check out my full guide here: Mastering the Excel XLOOKUP Function
How does the INDEX MATCH formula work?
The INDEX MATCH formula is the combination of two functions in Excel: INDEX and MATCH:
- INDEX finds a cell’s value in a table using its column and row number
- MATCH finds the position of a cell in a row or column
Together, they can find and give you a cell’s value in a table by looking up both up and down, and left and right. This is known as the Index and Match formula.
What does the INDEX function do?
The Excel INDEX function is like a quick search tool for your spreadsheet. It lets you easily find and show specific information from a big list or table.
Say you need to find a name in a long list or a specific figure in a budget, INDEX helps you locate this information fast.
The first argument of INDEX is to give it an array. This array (range) should include your answer.
You then need to specify how many rows to go down and how many columns to move to find the correct value. You cannot move outside the INDEX range.
The syntax of INDEX is:
=INDEX(array, row_num, [column_num])
- array: The range where the return value resides. You’re basically pointing to a block of cells and saying, “Hey, the information I want is somewhere in here.”
- row_num: You’re telling INDEX how many rows down from the top of your selected area to go to find the information. If you say “2”, INDEX will move two rows down from the top of the area you pointed out.
- [column_num]: This is like the row number, but instead, you’re telling INDEX how many columns to the right to go. If you say “3”, it moves three columns to the right. This part is optional because sometimes you’re only interested in a single column.
In short, you’re giving INDEX directions like, “In this block of cells, go down this many rows and over this many columns, and show me what’s there.” That’s how the INDEX formula knows what specific piece of information to grab and show you.
Here is an example of how INDEX finds information. By moving down 4 rows in the range A2:A9, it finds the value “West_04”.
On its own, the INDEX formula is not that useful. How often will you actually know where something is in a spreadsheet? We need a smart way to figure out exactly where our needed data is hiding.
That’s where the MATCH function steps in. MATCH tells INDEX exactly where to go by giving it the row or column number.
Together, they quickly find the information you need.
What does the MATCH function do?
The Excel MATCH function is your quick-find tool in a sea of data. If you’ve got a list—any list, like names or numbers—and you need to know the spot where a specific item is, MATCH is your go-to.
It scans through your list and tells you exactly where your item sits, saving you from the headache of searching through row after row.
The Excel MATCH function is like a helper that tells the INDEX function where to find the information you’re looking for. Unlike other functions that tell you what’s in a cell, MATCH tells you where the cell is located in your list or table.
Here’s how you set up the MATCH function:
=MATCH(lookup_value, lookup_array, [match_type])- lookup_value (what you’re searching for): This is the piece of information you want to find. You can either point to a cell that has this information or type it directly into the formula.
- lookup_array (where you’re searching): Think of this as the list or table in your spreadsheet where MATCH is going to look for your item. It’s the area where you expect to find what you’re searching for.
- match_type (how precise you want to be): When you use “0” here, it means you want an exact match. That tells MATCH you’re looking for something that matches your lookup value perfectly, without any differences.
In this example, the MATCH function tells us the number 200 is found 4 rows down in the list we checked.
❗ Here’s something key to remember about MATCH: it looks through either a row or a column at a time, not both.
🌟 Tip: If you’re trying to use MATCH for multiple criteria, you’ll need to make your formula look in just one direction.
You can do this by combining MATCH with another INDEX function or by changing your formula so Excel treats it as a special array formula (CSE – this just means pressing Ctrl, Shift, and Enter together after typing it in).
Want to see how it’s done? Check out the second part of our guide.
How to combine INDEX and MATCH (step by step)
Now, let’s see how to use INDEX MATCH as a team.
- First, MATCH searches for the specific item you’re looking for and figures out where it is — like finding the row or column number.
- Then, INDEX uses that location to grab the exact piece of information you need.
Below, we’ll show you an example of how to do INDEX MATCH to make finding data easy.
=INDEX(A2:A9, MATCH(200, B2:B9, 0))
You can also tweak this formula to figure out who had the highest sales. Just use the MAX function inside the MATCH function. This way, you find out where the top sales number is located.
=INDEX(A2:A9, MATCH(MAX(B2:B9), B2:B9, 0))
VLOOKUP needs you to include all columns in your search area, even the ones you don’t need. This can slow things down, especially with big spreadsheets.
INDEX and MATCH are different because you only have to point out the column where your answer is. This can make things faster.
A simple way to think about using INDEX and MATCH together is like this:
=INDEX (where my answer is, MATCH(what I’m searching for, where to look for it, 0))This formula helps you find exactly what you need, quickly and efficiently.
How to do a two-way lookup with INDEX MATCH MATCH
Use INDEX MATCH & a second MATCH (the INDEX MATCH MATCH formula) in cases where you need to look up a value in a matrix. MATCH is used twice:
- once for the rows, and
- once for the columns
In this example, the return range in the INDEX function involves multiple columns and the MATCH function instructs the formula to move down a certain number of rows and move over a number of columns across the sheet to retrieve the desired value.
Instead of just selecting a single row or column you need to index the entire table with multiple rows and columns as your array since your value is somewhere inside this matrix.
=INDEX(A1:D9, MATCH(A11, A1:A9, 0), MATCH(B11, A1:D1, 0))
The syntax of the INDEX MATCH MATCH formula is:
=INDEX(Full range with the answer, MATCH(Value I’m looking for in the rows, Range I need to find the Value, 0), MATCH(Value I’m looking for in the columns, Range I need to find the Value, 0))How to use INDEX MATCH inside SUMIFS
You can mix INDEX and MATCH into other formulas to do even cooler stuff.
For example, you can put them inside a SUMIFS function. This lets you add up numbers from either the sales or volume column. It depends on what you choose in cell G4 (Volume or Sales).
Here’s how it works:
- If you pick “Volume” in G4, it adds up the volume numbers.
- If you switch G4 to “Sales,” it then adds up sales numbers instead.
The numbers it adds are based on specific conditions: they must match “West_01” in column A, “2024” in B, and “Jan” in C.
Changing the choice in G4 changes what gets added. This trick is great for making dynamic dashboards that update automatically.
=SUMIFS(INDEX(A2:E20, , MATCH(G4, A1:E1, 0)), A2:A20, H1, B2:B20, H2, C2:C20, H3)- Selecting Volume in G4:
- Selecting Sales in G4:
How to Use INDEX MATCH with Copilot in Excel
If you have Microsoft 365 Copilot, you can generate INDEX MATCH formulas in plain English instead of writing them manually. This is useful when you forget the argument order or need a quick two-way lookup.
Steps:
- Select a cell in your data range
- Click Edit with Copilot in the Home tab
- Type a prompt like: “Create an INDEX MATCH formula that returns the sales rep name from column A based on the sales value in column B”
- Copilot drafts the formula. Review it, then insert into the cell
Copilot handles nested MATCH functions reliably for single-criteria lookups.
For multi-criteria lookups or matrix lookups, we still recommend writing the formula manually so you understand the range references. See our guide on INDEX MATCH with multiple criteria.
Want to go beyond lookups and build a complete Excel foundation? Our Excel Essentials for the Real World course covers INDEX MATCH, VLOOKUP, conditional logic, and the everyday skills professionals actually use at work.
Featured Course
Excel Essentials for the Real World
INDEX MATCH FAQs
How do I use INDEX MATCH in Excel?
Use the formula =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). INDEX selects the column with your answer, and MATCH finds the row position of your lookup value. Always set the match_type to 0 for an exact match.
Why is my INDEX MATCH formula returning #N/A?
The #N/A error means MATCH can’t find your lookup value. Check for extra spaces, mismatched data types (text vs number), or a lookup value that doesn’t exist in the lookup range. Wrap the formula in IFERROR to return a custom message: =IFERROR(INDEX(…), “Not found”).
Can INDEX MATCH look to the left?
Yes. Unlike VLOOKUP, INDEX MATCH works in any direction because the return range (INDEX) and lookup range (MATCH) are independent. You can return a value from column A while searching in column D.
What’s the difference between INDEX MATCH and VLOOKUP?
VLOOKUP only searches left to right from the first column and uses a fixed column number that breaks when columns shift. INDEX MATCH searches in any direction, survives column insertions, and runs faster on large datasets because it only references the columns you need.
Should I use INDEX MATCH or XLOOKUP?
Use XLOOKUP if you’re on Microsoft 365 or Office 2021 and later. It has simpler syntax, a built-in exact match default, and handles errors natively. Use INDEX MATCH if you share files with users on older Excel versions or need a two-way matrix lookup.
Download the INDEX MATCH practice file
Get the Ready-to-Use INDEX & MATCH Template
Don’t build complex lookups from scratch. Download the finished template and plug in your data to see it in action.
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
