Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Using a combination of the INDEX() and MATCH() functions to dynamically restate the dashboard source information
Download this lesson’s related exercise files.Pinpoint data with INDEX(MATCH()).xlsx
50 KB Pinpoint data with INDEX(MATCH()) - Completed.xlsx
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.
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
- 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
- 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.