Excel and Google Sheets Tips

How to Index Match in Microsoft Excel or Google Sheets. A Useful Tool for Combining Data Using a Unique Identifier.

Example of Index Match in Google Sheets

VLookup is a useful function for searching through a spreadsheet and getting information for specific tasks. I have found INDEX and MATCH combined as useful and much less restrictive.

Example 1: Using the first name to look up the last name.

In the Gif above, I type the first name in the orange block and it returns the first corresponding last name from the Last_Name column.

Here’s how you do it:

Start with your table:

Sheet 1 Employee Information

Where you want your Information to be displayed type your formula. I have placed the name to look up in the cell in the previous column as seen in the image below.

Cells showing information

The formula for this specific task is:

INDEX MATCH Formula with Example

Here it is in Google Sheets. I have added the boxes to help show where the information in the formula is coming from:

Example with color boxes showing formula information

Once the formula is set I can type in a new name from column B and it will display the last name without having to touch the formula.

This was a really simple use of the function. Let’s take a look at another use for this function.

Example 2:

Let’s use our same four employees from the above example but we have a contract sheet where we want to add a column with the middle names.

Starting sheet Example 2

As you can see I have added the full names over to the right to help visualize it better for you.

So in this one, we are going to use $. No, not money but an absolute value. An absolute value is a value that doesn’t change. If you look at the following image you will see what happens when I drag the formula down the column.

Sheet showing the formula without absolute values

The reason for the error is the Index is incrementing as I drag down the column so there is nothing for the function to reference.

Let's try it with some $ absolute values.

GIF showing result of formula with $ Absolute value

That works better.

Here is the formula with absolute values that I used for this:

=INDEX($G$2:$G$5,MATCH(A2,$F$2:$F$5,0))

As you can see I didn’t lock down A2. The reason is that I actually want that one to increment. As I pull down the column copying my formula, it changes to A3, A4, A5, and so on. That way it is looking at the correct first name.

Sheet showing the formula with boxes.

As you can see there are many uses to this Function. It works the same in both Google Sheets and Microsoft Excel.

Thank you for reading!