1 Excel Formula to Save Your Team Hours:
Does your organization utilize the Microsoft Suite, as a great number of organizations do? Do you need to use Microsoft Excel to manage and analyze your data? If so, there is a strong chance that you and your team have had to complete a monotonous task in Microsoft Excel. In this post, I will share a key formula that will expedite one of the most common tasks in Microsoft Excel: matching information between worksheets. Now you may be thinking, “well can’t you just sort the data on both sheets, then copy and paste the data you want to match in?” The answer to that is rarely. In order for that solution to work, your data sets need to be identical, which more often than not, is not the case.
Let’s take a look at the data in Exhibit A below. Our goal is to match in the Country of Work for each Employee Number in Worksheet 2. As you can see, the data sets are not identical, so we cannot just sort both data sets then copy and paste.
Instead of matching in the Country of Work data one Employee Number at a time, we can use a formula that is referred to as an Index Match.
Range of Desired Values – This is the range of values you want to match into the new worksheet. In Exhibit A, this would be the Country of Work range in Worksheet 1. After typing =Index( in the cell to the right of the first Employee Number in Worksheet 2, you will select the entire Country of Work range in Worksheet 1, starting from USA all the way down to Brazil. You will then finish this section of the formula by typing a comma after the range.
Unique Identifier – This is the unique value that is tied to the value you want to match into the new worksheet. In Exhibit A, this would be the first Employee Number in Worksheet 2. Following the comma that you typed after the first range (above), you will type the word Match( then select the very first Employee Number in Worksheet 2. This would be Employee Number 8765. You will then finish this section of the formula by typing a comma after this Employee Number.
Range of Unique Identifiers - This is the range of unique values that Excel will use to locate the unique value you identified in the previous section of the formula. In Exhibit A, this would be the Employee Number range in Worksheet 1. Following the comma that you typed after the Employee Number (above), you will select the entire Employee Number range in Worksheet 1, starting from Employee Number 1487 all the way down to Employee Number 8765. You will then finish the entire formula by typing a comma, the number 0, then two parenthesis.
IMPORTANT: Before extending the formula down, which you do by double clicking the bottom right corner of the cell where you typed the formula, you absolutely must lock the Range of Desired Values and Range of Unique Identifiers. To lock these ranges, in the formula, you click the first cell of the first range (C4) and hit the F4 button once. You then proceed to do the same for the second cell of the first range (C12), the first cell of the second range (B4), and the second cell of the second range (B12).
The finished Index Match formula should look like the formula in Exhibit B below.
After extending the Index Match formula down to the last Employee Number in Worksheet 2, your final product will resemble that of Exhibit C below.
As you can see in Exhibit C, every Employee Number in Worksheet 2 now has its corresponding Country of Work next to it based off of the data in Worksheet 1. You will notice that some Employee Numbers in Worksheet 2 have a #N/A for their Country of Work. These Employee Numbers have a #N/A because they are not part of the Employee Numbers in Worksheet 1.
That is it, you now know how to Index Match! Simple, right? You may be thinking, “wow, that formula is so complex, I could have matched the Country of Work into Worksheet 2 way faster one Employee Number at a time.” This may be true for the data above, but now imagine that instead of the 13 Employee Numbers in Worksheet 2, you have hundreds or even thousands of Employee Numbers and Countries of Work. Without the Index Match formula, matching in the Country of Work would take so much time, if not be impossible. The time that your team can save by using this 1 formula is time they can allocate to much more meaningful tasks, which will ultimately increase the value of your team to your organization.
Are you interested in learning more formulas that can save your team valuable time or need assistance training your team to use these Microsoft Excel formulas? Learn more below!