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.
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:
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.
The formula for this specific task is:
Here it is in Google Sheets. I have added the boxes to help show where the information in the formula is coming from:
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.
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.
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.
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.
That works better.
Here is the formula with absolute values that I used for this:
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.
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!