How to use INDEX MATCH function, one of Excel's most powerful features

In our latest 3 tip Friday newsletter read about Excel's Flash Fill feature, shortcut which adds borders to the selected area and INDEX MATCH function, one of Excel's most powerful features.

In our latest 3 tip Friday newsletter read about Excel's Flash Fill feature, shortcut which adds borders to the selected area and INDEX MATCH function, one of Excel's most powerful features.

Excel’s Flash Fill feature

Excel Flash Fill feature will autofill a column based on previous entries. Excel will detect the pattern from the previous entries and autofill the rest of the cells in a column.

In the example below, enter the initials in the first cell of column C:

Excel’s Flash Fill feature

Then go to Data on the toolbar and click on Flash Fill in the Data Tools section.

Excel’s Flash Fill feature

You will see the rest of the range in the column C will be filled with the initials from column A and B.

You can read more about this function here

Shortcut to add borders to a selected range

Adding borders to a range can be done using a quick shortcut:

  • Select the range you want to add the borders to

  • Press Alt+H

  • Type B

  • The following window will popup:

image7.png
  • Click A to add borders to all the cells in the range or type one of the other letters to choose a different border type.

To get a full list of Excel shortcuts, check out the Microsoft help article.


Index Match function

The Index and Match functions in Excel are very often used together to retrieve a specific value from a range. The Index function itself can retrieve a value from a range but you will need to know the exact position of the value you want to retrieve. There are three arguments to the Index formula:

array: this is the range where we will be looking for our value

row_num: this specifies the row where our value is (optional if the range selected is just one row)

column_num: this specifies the column where our value is (optional if the range selected is only one column)

In the example below if we want to know what country is ranked 4th, we would enter the following formula in cell E2:

The Index function itself can retrieve a value from a range but you will need to know the exact position of the value you want to retrieve. There are three arguments to the Index formula:

The row and column numbers are relative to our range: although the 4th position is in row 5, within range A2:C11, it’s in the 4th row from the top of the range.

The column_num is 2 since we are retrieving the country name, which is in column two in our range.

Now let’s try and get the turnover for Germany. Our example is pretty small and we can quickly see that Germany is in position 8 in our range, but for a larger dataset, this is almost impossible. To achieve this first we will find in which row in our range is Germany. We can do this by using the Match function.

This function will return the position of a value within a range and it has the following elements:

lookup_value- this is the value for which the position we want to retrieve (in our example, this is “Germany”

lookup_array- this is the array where we are looking for this value (in our example this is B2:B11)

match_type- this is an optional argument, you will mostly use 0 as an argument as this will give you the position of the value that is exactly the same as the lookup_value

Enter the following formula in cell E5, this will return the value 8, which is the position of Germany:

image2.png

Now we will join these two functions to retrieve the turnover of Germany by replacing the row_num in the Index formula with our match function as follows:

image1.png

This formula will return 9000 which is the turnover for Germany.

You can read more about these functions in the following links:

MATCH function - Office Support

INDEX function - Office Support

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit