< Go Back

Basic INDEX MATCH exact

General Formula

=INDEX(data,MATCH(value,lookup_column,FALSE),column)

Explanation

In the above example, INDEX and MATCH are used to gather information from the table based on an exact component that matches the criteria given.

How the Excel Basic INDEX MATCH exact function formula works

The Excel Basic INDEX MATCH exact function formula depends on MATCH to get the position on the row for Hannah in the table while INDEX is used to retrieve the value at that row in the second column ‘year’.

  • MATCH in this formula is designed to search for the value in H5 in the year column

MATCH(H4,B5:B9,FALSE)

  • The last argument is named ‘FALSE’, and this forces the MATCH function to perform only the exact match on the second column ‘year’.
  • MATCH then finds “Hannah” on row 4 and therefore returns this number to INDEX to represent the row number.
  • The INDEX function is also designed to be an array that carries all the data present in the table, the column number is matched as 2 based on the whole data that was selected in the table. So, once 4 is returned by MATCH, we have:

=INDEX(B5:E9,4,2)

  • Where there is an intersection between the 4th row and the 2nd column, the INDEX function then uses the value, which is matching exactly with “2013”.

Once this is okay, other formula remains the same except for a change in the column number. For instance, we use the following formula below to know the exact year, average and position of Hannah.

Example 1

Figure 1: Example showing how to use the Excel Basic INDEX MATCH exact function to locate the position of Hannah with an average of 82 in the year 2013.

The formula in cell H8 is:

=INDEX(B6:E10,MATCH(H5,B6:B10,FALSE),4)

And this returns 2013 as the year Hannah took 5th position.

Example 2

Figure 2: Example showing how to use the Excel Basic INDEX MATCH exact function to match the exact year Hannah had an average of 82  with 5th position

The  formula used is:

=INDEX(B6:E10,MATCH(H5,B6:B10,FALSE),2)

This match the exact year for Hannah

Example 3

Figure 3: Example showing how to use the Excel Basic INDEX MATCH exact function to match the exact average of Hannah in the years 2013 with 5th position

To match the exact average for Hannah, use the formula:

=INDEX(B6:E10,MATCH(H5,B6:B10,FALSE),3)

Example 4

Figure 4: Example showing how to use the Excel Basic INDEX MATCH exact function to match the exact  position of Hannah in the years 2013 with an average of 82

The formula used is:

=INDEX(B6:E10,MATCH(H5,B6:B10,FALSE),4)

To match the exact position for Hannah,

Using the Excel Basic INDEX MATCH exact function with a single column

The example above employs INDEX for an array that contains the entire data in the table. However, you can just rewrite the formulas to work only with just a single column and this eliminates the need for a column number:

Using INDEX for a single column, use the formula below:

=INDEX(C6:C10,MATCH(H5,B6:B10,FALSE)) for the exact year

=INDEX(D6:D10,MATCH(H5,B6:B10,FALSE)) for the exact average

=INDEX(E6:E10,MATCH(H5,B6:B10,FALSE)) for the exact position  

In each of the cases seen above, the Excel INDEX function takes a single column array which is corresponding to the data that is being retrieved while the MATCH function is responsible for supplying the row number.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar