Download the free workbook 👉 HERE and follow along.
INDEX MATCH vs VLOOKUP
VLOOKUP is one of the most popular lookup function 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.
The INDEX MATCH formula is much more powerful. It can look in any direction, left or right, and find more than one match. This makes it more flexible for different Excel tasks. We’ll look at some INDEX MATCH examples in this post.
How Does INDEX MATCH 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.
How to Use the INDEX Function in Excel
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.
How to Use the MATCH Function in Excel
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.
Featured Course
Excel Essentials for the Real World
INDEX MATCH Explained
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.
Two-Way Lookup with INDEX and 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))Building on the INDEX and MATCH Function
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:
Download the Workbook
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the INDEX MATCH in Excel with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.
More Learning
For advanced uses of INDEX MATCH in Excel, including handling multiple criteria and dealing with errors, check out this comprehensive guide. Learn powerful techniques to enhance your data analysis skills and streamline your workflows.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
