Easy Search and Match with Index Match in Excel and Google Sheets

Your vision is to know Excel’s most powerful features, one of which being the lookup tool of index and match.

You often need values looked up based off of rows and columns. You may or may not be familiar with vlookups, which use a single column to match to. However, you have found that at times there are limitations with that and you would like to know about more advanced lookup options. This is where the combination of index and match come in. This article will work through a sample in Google Sheets but Excel works similarly.

Sample spreadsheet

Index and match vs. vlookup in Sheets and Excel

How does index work?

Index is like a GPS and you need to choose a map, which is an array, or all of the cells you want to select for the function to look at. The purpose of this is to return a value that is in your table or range.

How does match work?

Match will allow you to lookup a value in a single column. You may choose an exact match or less than or greater than. If you use this alone, the answer you get from this is going to be a number that tells you the position.

Now, index and match get merged together for an example

We want to find the product name and cost of the items that customers 1, 8, and 20 purchased. We will use index and match together because this allows you to look up values in your table using other rows and columns.

Step 1: Enter 1, 8, 20 in separate rows in column J.

Step 2: We are going to use this formula in multiple cells, so make sure you include the $ to freeze the correct cells. Enter the formula in K2 to first index(, so choose column B because that is the column you want to search to find the product name.

Step 3: Next, type match( and select J2 because we want to find the product name that customer 1 purchased. Then add column A: to match to because that is where customer ID is located, and enter 0 because we want an exact match. Your formula should look like this.

=INDEX($B:$B,MATCH($J4,$A:$A,0))

You should see shirt 1 in K2.

If you want to drag that down, grab the lower right corner and do so. You’ve frozen your cells with the dollar signs so you don’t have to worry, they are pulling from the correct cell.

Repeat the steps for cost. We chose column D for index because that is where cost is located. We then match to J2 again, because we want to start with finding the cost paid for the product name that customer 1 purchased. The column is again A:A because that is where we will find customer ID.

=INDEX($D:$D,MATCH($J2,$A:$A,0))

You should see $10.00 in L2.

If you want to drag that down, grab the lower right corner and do so. You’ve frozen your cells with the dollar signs so you don’t have to worry, they are pulling from the correct cell.

Why would you use index and match together over a vlookup?

Fewer errors than something like a vlookup.
If you add a column to your file, and it’s no big deal, whereas with a vlookup, your formula will break.
You can move columns around the sheet and nothing changes in your index and match.
You can use index match no matter how long your lookup values are, whereas vlookups are limited to 255 characters.
Index match is quicker than vlookups.
The match doesn’t have to be in the left column, it can be in rows or columns.
The column doesn’t have to be sorted for an index match to work.

In summary, vlookup and index match vary for the reasons above but both work well. I’ve heard index match referred to as the big brother to vlookup.

Try it yourself

Our example was done on a single spreadsheet tab so it’s easy to see. Most often, I use lookups when working with multiple tabs so you may find more use with this when dealing with more data. You could use the same tab as the sample, and try index and match to pull in the customer name from the “Customers” tab in column M. The solution is already there.

Try index and match the next time you need to perform a lookup and see how it works.

Written by Nicole Hullihen, January 12th, 2022