Index and Match Formulas Explained
Learn New Formulas in Excel and Be More Efficient
In our previous articles, we discussed various Excel functions such as count, counta, countblank, and others. Today, Hot Key Excellence will guide you on how to use index and match formulas by simply typing the necessary shortcuts.
Match formula in Excel is used to find the relative position of a value in a specific cell range. It means that we use Match formula to find the location of a cell. For example, we have a list of 10 people in cells between B1: B10. The 8th name in the list is Mark. The Match formula will search for Mark name position in the list and return 8 as the result.
If we look at the example below in the pictures, there is a table where we can see the ranking of the population in three different cities. In this situation, we are trying to find out the population ranking of the New York City. Therefore, we selected cell B4 as the value that we are looking for. Then, we selected the range of all cities because this is where we are looking for New York’s location. And finally, we put 0 as the last parameter because we are looking for an exact match. As you can see, the Match formula returned the value 1, which is the relative location of New York in the list.
We use Index formula to find out the value of a cell in a cell range based on its row and column position in that range. In other terms, Index formula looks into a cell range, goes to the relative row and column position that we input. Then, it returns the value in the cell.
If we return to the example used in Match, we are trying to find out the population in the selected city. We changed the city from New York to London, so the Match formula returned 3 instead of 1. It shows that London is in the 3rd place in this table. We wanted to find the London population. Therefore, we selected the population column as the cell range that we are looking for the answer. Then, we need to find the location of the cell value that represents the population number for London.
For this step, we will use the value that we calculated with the help of Match formula. We found in the cell B5 that information about London in on the 3rd row in the cell range. Therefore, we selected B5 as the second parameter of the Index formula. It is the relative row position of the cell that we are looking for. Since the selected cell range has one column in it, we don’t need to fill the third parameter of the Index formula that is the relative column position of the cell.
Index formula successfully returned the value of the cell which is located in the 3rd row of the cell range F2: F4. Since the table is relatively small enough to check our answer, we can clearly see that Index formula worked properly. It returned the answer we were looking for.
Hot Key Excellence introduced you to a new method of combining the pleasure of playing games with the utility of learning. Try our free version and save your time by applying Excel shortcuts.