How to Use Index and Match in Excel

If you are looking to learn valuable skills that will help you advance in your career, one of the best places to start is Microsoft Office. Microsoft Excel, in particular, is the ruling product on the market for data. Any role that handles data can expect to use Excel, or similar spreadsheet software, to some extent. Storing data within the program is simple enough, but those looking to use Excel at an expert level will want to learn some of the most popular tools, functions, and formulas. 

One of the best ways to save time in Excel is being able to find information quickly. A great way to seem extraordinary at your job is to be able to respond to questions quickly. Impress your bosses and dazzle your friends! All through the magic of finding information promptly with popular Excel tools like the INDEX and MATCH functions.

Introduce yourself to Excel

Before learning functions like INDEX and MATCH, you should be casually aware of the basics of Microsoft Excel. You don’t need to be best friends with Excel, but you should have met it before. In other words, you should know how to open, setup, and save a spreadsheet.

To use functions, you will also need to know about cells and cell references and how to work with cells. I recommend checking out the most important advanced Excel skills for professionals.

INDEX and MATCH are both functions within Microsoft Excel. You may want to check out some courses and articles to get more comfortable with Excel and use the functions and formulas. If you are looking for an awesome introduction to Microsoft Excel, sign up for a course. You can join thousands of students who are learning to understand the magic of Excel. 

Look it up: Excel tools for finding information

There are three great tools in Excel for finding pieces of information. These are VLOOKUP, MATCH, and INDEX. Excel stores data in the form of columns, or lists, of data. The Lookup function, VLOOKUP function, and HLOOKUP function allow you to sort through these lists of data. You can use them to find information that meets the criteria we are looking for.

MATCH and INDEX are both great places to start if you want to find information fast and look cool doing it. Or, relatively cool.

For example, say you have a giant spreadsheet with lots of information about each item. This might include quantities, sales, prices, storage bins, and more. You want to know about just one item in that inventory. With these functions, you can look up just one price, find where a particular item ranks in sales, or figure out which item sells the third most. 

Using Excel formulas and functions

The real magic comes from the formulas and functions of Microsoft Excel. Learn how a popular Excel function or formula works, and you will unlock the true power of Excel. The MATCH and INDEX functions will make finding information in large spreadsheets and worksheets as simple as one, two, three! Abracadabra!

Each function has a syntax. The syntax is the way you enter the information and what the information is. Once you understand the syntax, you can use the function.

What is MATCH?

Triumph. Exhilaration. Relief. These are the feelings you experience when you find something you’ve lost. Whether you doubt the emotional capacity of Microsoft Excel or not, this is exactly what the MATCH function in Excel can do for you.

In its purest essence, the MATCH function searches a range of cells for a specified item and then identifies its relative position. It can then return the position to you. You would use the Excel MATCH function in place of the Lookup function when you want an item’s location and not the item itself. It can’t bring it to you, but it can help you find it. You can use the MATCH function to find lost values, but you can also use it within other functions to provide location values.

The pieces of the MATCH function

We’ll take a closer look at the Excel MATCH function’s syntax, and then we’ll put it to use to see if it lives up to the hype. For all its divine glory, the MATCH function is a simple one. Its syntax is as follows:

=MATCH(lookup_value, lookup_array, [match_type])

Now let’s break down the arguments. The arguments are the parts of the syntax that a function takes in. They are the terms inside the parentheses.

Lookup value

The first argument you enter is the lookup value (lookup_value.) This is the lost item; the value that you want to “match,” as it were. The first argument you enter is the lookup value (lookup_value.) This is the lost item; the value that you want to “match,” as it were. Let’s say you had an enormous inventory of tempting objects. If you wanted to know, say, how many apples you had, you would be using “apples” as the look-up value, while the number of apples is the actual value desired.

To enter the lookup value, you can enter a cell reference by selecting or enter it yourself. The lookup_value can be a number or text. If it is text, you need to use quotation marks. You can also use a cell reference to a number or text.

Lookup array

The lookup array (lookup_array) is simply the range you want to search. You get this by selecting the range of cells you are using.

Match type

The match type (match_type) is the last argument and the only one that is not required. There are three possibilities: -1, 0, and 1 (1 is the default). You would use the match_type argument to specify how Microsoft Excel matches the lookup_value with the lookup_array.

While not required, the match_type argument is where you can add a little variety to the MATCH function.

• A “1” (or blank) matches the largest value that is less than or equal to the lookup value. The only stipulation is that the values in the lookup array are in ascending order (for text, in alphabetical order; Apples, Chocolates, Ferraris, etc.). So the array arrangement is important.
• A “0” finds the exact match of the lookup value, and in this case, the values in the lookup array can be in any order.
• A “-1” lets you find the smallest value that is greater than or equal to the lookup value. As you might expect, the values in the lookup array must be in descending order (for text, in reverse alphabetical order; Ferraris, Chocolates, Apples, etc.).

Practicing MATCH

Ok. Let’s have a look at our tempting objects spreadsheet:

California is crazy these days, and bacon is flying off the shelves. Let’s locate Bacon and see how our inventory is holding up:

=MATCH(“Bacon”, A2:A7, 0)

The Excel MATCH function tells me that Bacon is fifth in the list of tempting objects. 

Now, let’s use the match type “1” to see if I have 10 of anything:

=MATCH(10,B2:B7,1)

It turns out that I don’t, but now I know where the next lowest value resides:

We can do the same thing with “-1” if we want to see the next highest value:

Or can we? We get an error because the lookup array is not in descending order. But that’s ok. We can use this as an excuse to review, as well as introduce of few of the nitty-gritty details of the Excel MATCH function.

How to avoid errors

So what went wrong? For starters, the Excel MATCH function finds the matched value’s relative position within the lookup array (or range), not the value itself.

For example:

=MATCH(“Apples”,B2:B7,0)

would return 1, meaning Apples is in the first position of our range. If you have not ordered the lookup array appropriately relative to the function, it will return an #N/A value. You can avoid this by making sure to order the lookup array properly before you start.

What other issues can come up? What if you need something but do not know exactly what you want to match? When the match type is 0 and the lookup value contains text: use a question mark (?) to match a single character, and an asterisk (*) to match a sequence of characters. This will help you find an approximate match. But what if you’re trying to match a question mark or asterisk? In that case, precede it with a tilde (~).

There are some problems it takes a human, not Excel, to avoid. Unfortunately, you cannot use the Excel MATCH function to distinguish between uppercase and lowercase letters. So that’s something you need to prepare for.

If you have a list that contains duplicate values, the MATCH function can only find the first occurrence of the value you are matching. You would use a match type of “0” for an unsorted list, but this would still yield only the first occurrence. You would use a match type of “1” for a list in ascending order if you wanted to find the last occurrence of the match value. And using a “-1” for a list in descending order would also reveal the last occurrence.

What is INDEX?

So now that you have met the thrills and excitement of the MATCH function, let’s talk about the other great tool for finding information, INDEX.

In its purest essence, the INDEX function is the opposite of MATCH. What INDEX does is looks at an array. You would use the Excel INDEX function when you want the data at a specific location. This time we don’t know what’s in that spot, but we do know what place we want to look in. Think of it like this. Instead of looking for where your keys are, you are wondering what you have in your pocket. Just like with the MATCH function in Excel, some of the best value from INDEX comes from what it can do in other functions once you are a pro at it.

The pieces of the INDEX function

We’ll take a closer look at the Excel INDEX function’s syntax, and then we’ll put it to use to see if this function also lives up to the hype. Now that you know how to use MATCH, the INDEX function is a simple one. The syntax of the INDEX function is:

=INDEX(array, row_num, column_num)

Now let’s break down the arguments of this function

Array

The first argument you enter is the array (array.) This is the range of cells you are looking at. It can be a whole spreadsheet, one column or row, or just a few cells. An array is a range of cells, or a list of them. Each array you put in will be the range of cells.

To enter the array, you can type in the cell range you want or select the cells.

Row_num

The row number (row_num) is the row you want. Row num is short for row number. That is the number of the row we want to search. We need to tell Excel what the row number is that contains the data we are searching for. To work out the row number, you just need to look at the far left of the row you are looking at, and they have numbers.

Column_num

The column number (column_num) is the last argument and the only one that is not required. That is the number of the column we want to search. If we’re going to include it, we need to tell Excel which column number contains the data we are searching for. To work out the column number, count from left to right and enter the column number that contains the information you want it to return. You can also convert from the letter of the column to a number.

If you’ve only selected one column, you can skip this step and your index step will still return a value to you.

The value the INDEX function returns will be the contents of the cell that we search.

Practicing INDEX

Ok. Let’s have another look at our tempting objects spreadsheet:

We know the third object on our list is Ferraris, but we can’t remember what we put fourth on the list. We need this information so we can tell our friend where Ice Cream ranks. Or, maybe we have a list ranked by inventory, and we want to know which tempting object we have the fourth least of. Either way, we enter our index function.

We select our entire data set as our array, A1:B7, and then we check and see that we want row five (since the first row is a header) and column one.

So our formula looks like this:

=INDEX(A1:B7,5,1)

The Excel INDEX function tells me that Ice Cream is the item that is fourth on our list of tempting objects.

Now, let’s see how many ice creams we have by checking column two:

=INDEX(A1:B7,5,2)

It turns out we have 61.

Now we can check out column three to get the next piece of information:

Or can we? We get a new error #REF because we are looking outside of what our information has. We are only using two rows, but we tried to check the third one.

You can avoid this by making sure the data you are looking for exists before you start.

Moving beyond searching

The Excel MATCH and INDEX functions may not help you find your car keys, but you can use each in various situations. They will make you drastically more efficient when searching through long lists.

If all this searching has you excited, you might want to think about search from another angle. Why not diversify your talents and learn everything essential about search engine optimization? 

You could also dive deeper into the Excel waters and start learning all the ways to do data validation and text functions to get better at working with your data. You could explore the islands of pivot tables and bar graphs to make your data look amazing. Or you could go hard with data in Excel and start creating codes with VBA.

There are many paths to find now that you can find information!