# 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

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)) 