davesexcel.com

Excel information

Using Index/Match to return a result in Excel

Understanding the Index function

This is the workbook i am using for this example

Index_Match.xls

 

You can use Index Function to return a result. In the figure below

If we wanted to return a result from the 2nd row in range B2:B5  we will use

=INDEX(B2:B5,2)

We find that the result is A3.

 

 

 

Undestanding the Match Function

As you have noticed in the Index Function we use row and column to returns a result.

 The majority of the time we do not know what the row and column is though.

We will use the Match Fuction to find this out.

Lets Find a row in a Specified range.
In range A2:A6 we want to know where C is located.

=MATCH("C",A2:A6,0)

 

What Column is Q located in Range B1:E1

=MATCH("Q",B1:E1,0)

The two formulas

 

 

 This is how we will find the Row and Column for the Index Function.

 

 

Nesting the Index\Match function

 Row Headers and Column Headers.

We can now combine the Functions together.

Using the match function to determine what row to return the result.

 To return a result from just one column by locating the Row Header Number

 We want a result from range B2:B5, by finding out what row the value of F3's location is.

=INDEX(B$2:B$5,MATCH($F$3,$A$2:$A$5,0)) 

 

 This is the formula we will use to find Both row and column headers

=INDEX($B$2:$E$5,MATCH(F7,$A$2:$A$5,0),MATCH(G7,$B$1:$E$1,0))