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.

Index Function Excel

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.

Match function Excel

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

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 a breeze.

index match excel

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 and match, excel, dashboards

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.

two-way lookup, matrix lookup with Index Match Match

The syntax of the INDEX MATCH MATCH formula is:

=INDEX(The full range where your answer can be found in, MATCH(The value I’m looking for in the rows, The range I need to find the Value, 0), MATCH(The value I’m looking for in the columns, The 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, depending 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, “2014” 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.

index and match, excel, dashboards

Download the Workbook

INDEX MATCH in Excel is one of my favorite lookup formulas because it can be used in many complex situations. A complete understanding of this function is what separates the gurus from the merely capable.

Feel free to Download the Workbook HERE.

Excel Download Practice file

Check out Part 2 of this guide, where we go into more advanced uses of INDEX and MATCH.

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.