## About this lesson

Using a combination of the INDEX() and MATCH() functions to dynamically restate the dashboard source information

## Exercise files

Download this lesson’s related exercise files.

Pinpoint data with INDEX(MATCH()).xlsx50 KB Pinpoint data with INDEX(MATCH()) - Completed.xlsx

50.2 KB

## Quick reference

### Pinpoint Data With INDEX(MATCH())

Leveraging the INDEX() and MATCH() functions together.

### When to use

A combination of INDEX() and MATCH() can be used to dynamically restate your data, based upon user inputs. This function combination is essential for creating interactive dashboards.

### Instructions

#### The INDEX() function syntax

- Excel Syntax: =INDEX(array,row_num,[col_num])
- What it does: allows you to retrieve a data point from a rectangular range based on its x/y coordinates

#### Basic Example

- Assume a range of B2:B4 that holds “Cat, Dog, Mouse”
- The following formula would return “Dog”: =INDEX(B2:B4,2,1)
- Why: We are returning the second row from the first column of the data set

#### Why INDEX and MATCH compliment each other

- Since MATCH() returns a value, this function can be used to feed the INDEX() functions coordinates

#### Example of INDEX and MATCH

- Assume A1 = “Dog”
- Assume A3 = “March”
- Assume a table from B6:F18 that has month names in column B and animal names across row 6
- You need to return value of the intersection between the Dog column and the March row
- =INDEX(B6:F18,MATCH(A3,B6:B18,0),MATCH(A1,B6:F6,0))

##### Why?

- B6:F18 is the range of data that holds the entire table we want to look at
- The 1st MATCH function looks for “March” in B6:B18 and returns the numeric position of that data indicating the row to look across
- The 2nd MATCH function looks for “Dog” in B6:F6 and returns the numeric position of that data indicating the column to look in

### Hints & tips

- If copying the INDEX/MATCH combinations to multiple cells, it is a good idea to lock the data tables in using absolute referencing
- To avoid having to use absolute referencing for the tables, point to named ranges or Excel tables

Lesson notes are only available for subscribers.