Pinpoint Data with INDEX(MATCH())

Locked lesson.

Upgrade

About this lesson

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

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
Login to download
  • 00:04 We're now gonna look at another dynamics function which is called index.
  • 00:09 And this one's gonna work the opposite way of what we did with match,
  • 00:12 although they're very complimentary as you'll see.
  • 00:15 I'm going to go and drop into the cell here, category.
  • 00:18 I'm going to put in number four.
  • 00:20 What if I want to restate this particular cell,
  • 00:24 in order to actually pick up the fourth category?
  • 00:27 Well, the function for working with this is called equals index.
  • 00:31 And what index does, it takes three parameters, potentially.
  • 00:34 We're going to work with this top format here.
  • 00:37 What it says is what's the array?
  • 00:39 Well, the array here for me is gonna be my products list.
  • 00:42 And then it says, what row number would you like?
  • 00:45 At which point I’m gonna pick up the category number.
  • 00:48 I optionally have the column number but there's only one column here.
  • 00:51 So I'm just gonna close my parenthesis and hit Enter.
  • 00:54 And you'll notice this brings me back to accessories.
  • 00:57 If I were to go and change my category to one it will bring me back tents and
  • 01:01 two it will bring me back sleeping bags.
  • 01:04 So that actually works out quite nicely.
  • 01:07 Now what if I wanted to go and
  • 01:09 control also to say how do I pick up a date if were to put in say seven?
  • 01:15 Maybe I want it to pick up July.
  • 01:17 Being the seventh month, that would make sense.
  • 01:19 So I'm gonna come over here.
  • 01:20 And I'm gonna say equals index.
  • 01:24 And I'll select my data range.
  • 01:29 Comma.
  • 01:30 And it says what row?
  • 01:31 Well, there's only one row.
  • 01:33 And it's not an optional parameter here.
  • 01:35 As you can see, there's no square brackets in the IntelliSense.
  • 01:38 So I'm gonna type in my one, because I have to have that.
  • 01:41 And then it says, comma, what column number would you like?
  • 01:44 Well, this is where I'm going to pick up seven and close the parentheses.
  • 01:49 And when I hit Enter, you'll notice that it comes back with July.
  • 01:52 I can also go and pick up number four, which would return April.
  • 01:56 So if I know my numeric position,
  • 01:57 it's very very easy using index to actually get some information back.
  • 02:02 Why is this so useful and important to us?
  • 02:04 It's because we can actually combine index and
  • 02:07 match together to restate our dashboard data.
  • 02:10 Let's say that I want to restate this every time somebody changes sleeping bags
  • 02:15 so that I actually pull back the appropriate data.
  • 02:17 From the big table up above, whether it's tarps, or tents, or whatever.
  • 02:22 So the way that I can do this is I can say equals index.
  • 02:27 Open parenthesis, and it says, where's the array of your data?
  • 02:30 Well, it happens to be this great big large range of this entire say data set.
  • 02:35 Now it's always really important if you're gonna copy your formulas around
  • 02:39 to make sure you lock them in by pressing F4 so
  • 02:41 that you get them set up with the dollar signs to make them absolute.
  • 02:45 So that's the data range, it's that big two dimensional array.
  • 02:49 Now the question is, well, what row number and column number do you need?
  • 02:53 Well, this is where the match function actually comes in really,
  • 02:55 really useful, because what are we looking for?
  • 02:58 Well, we're looking for a match where it equals sleeping bags.
  • 03:01 So I'm going to lock that in by pressing F4, and
  • 03:05 it says, where would I find sleeping bags?
  • 03:07 Well, I'd find sleeping bags by looking in this entire set right here.
  • 03:15 And we'll lock that in as absolute as well and then will say comma,
  • 03:19 give me an exact match.
  • 03:22 Now the challenge with this is that if I'm looking for my actuals for
  • 03:25 row 38 what's the numeric position sleeping bags going to bring me back?
  • 03:30 It's going to bring me back row ten.
  • 03:32 So, I actually need to add one to this to move down a row to get into my revenue.
  • 03:39 Comma.
  • 03:40 So that will give me the correct row.
  • 03:41 And now I need to match and say which column do I want to pull back.
  • 03:47 Because I've started writing my formula from January I'm
  • 03:50 gonna click on January and I'll lock this guy down here as well.
  • 03:56 Actually let's lock him into just the row.
  • 03:58 Cuz maybe we can copy this formula down in a second.
  • 04:00 And then we're gonna say, where's the look up array for that?
  • 04:05 And this is the tricky part with this.
  • 04:06 Cuz remember that every table is a two dimensional array.
  • 04:09 Notice how we selected this entire block to start with.
  • 04:12 We also started when we looked in our row blocked.
  • 04:14 To go from all of column A.
  • 04:16 To look in our columns we start again in column A and come all the way across.
  • 04:20 So there's three sections to an index block.
  • 04:23 It's a two dimensional array that has your data area.
  • 04:25 On the left hand side you've got your row headers, and
  • 04:28 on your top row you've got your column headers.
  • 04:31 I'm gonna lock this guy in as well and say comma zero for an exact match.
  • 04:37 Close my parenthesis twice.
  • 04:40 And at this point, because I selected the entire row here and
  • 04:44 wrote my formula from this perspective.
  • 04:46 Because I set up all of my formulas with correct
  • 04:50 relative references, I can hit CTRL+Enter.
  • 04:55 And lock this formula in.
  • 04:57 It's now restated my data for sleeping bags.
  • 05:00 I should also now be able to actually copy this formula and paste it down one row.
  • 05:07 Of course it gives me the same data, why,
  • 05:10 because I offset when I found sleeping bag down one row, which pulled the revenue.
  • 05:15 I want to go down two rows to get the budget.
  • 05:18 So all I should need to do, is go back in, change my plus one to make it plus two.
  • 05:24 CTRL+Enter and at this point we should get the correct budget data.
  • 05:29 How cool is this?
  • 05:30 Well, now I can say give me the data for tents and
  • 05:36 it should return the right values.
  • 05:37 Go and change one thing, give me the data now for tarps and ground sheets and
  • 05:41 it restates the entire piece.
  • 05:43 And if you notice that the chart on the right hand side, because the chart is
  • 05:48 based off of this data, it updates right away based on the choice I made here.
  • 05:53 And this is actually one of the best practices when you're building your chart
  • 05:57 is just to actually set up a specific data range to chart from.
  • 06:01 Because you can then make that dynamic to pull from your original source data and
  • 06:05 get it into the format that you want.
  • 06:07 This is ideally set up for the chart.
  • 06:09 Everything is ideally set up for humans to maintain.
  • 06:12 So this is the secret of working with index combined with match to pull back
  • 06:16 the specific data points you need.

Lesson notes are only available for subscribers.

Gift this course