Understanding the MATCH() Function

Locked lesson.

Upgrade

About this lesson

The MATCH() function may not look like much, but it can pull back the position of a data element: something that is key for dynamic dashboarding

Quick reference

Understanding the MATCH() function

Working with Excel’s MATCH() function.

When to use

The Match() function is used to look up where a certain piece of data is stored within a data set, and will return the numeric position of that data in the data set.  (This function is often used in combination with the INDEX() function).

Instructions

The MATCH() function syntax

  • Excel Syntax:      =MATCH(lookup_value,lookup_array,[match_type])
  • What it means:    =MATCH([what should it look for],[where is the table to look in],[guess if it can’t find a match])

Returning an Exact MATCH()

  • This technique is used where you need to know the position of the lookup_value in the lookup_array, and want to know if the lookup_value does not exist
  • To return an Exact positional match, the final parameter (match_type) should be set to 0
  • If the lookup_value cannot be found, a #N/A result will be returned

Returning an Approximate MATCH()

  • This technique is used where there are gaps in the data (you are searching for a lookup_value that may fall between two data points)
  • To return an Approximate positional match, the final parameter (match_type) should be set to -1 or 1
  • When using -1 as the final parameter, the lookup_array must be sorted in Ascending order
  • When using 1 as the final parameter, the lookup_array must be sorted in Descending order
  • An approximate match will return a match for the first row that is not in excess of what you’re looking for

Hints & tips

  • To provide a friendly message in case an exact match is not found, use =IFERROR(<match>,”Not Found”)
  • It is a best practice to always declare the final parameter so you don’t get burned by the default when it is not what you want
Login to download
  • 00:05 We're now going to start laying the groundwork for making a dynamic dashboard,
  • 00:09 something where we can click a control and
  • 00:11 have it restate the entire dashboard to only show the stuff we want to see.
  • 00:15 Now that sounds cool, but the problem is that there is some functions
  • 00:19 that we need to make sure that we understand before we move there, and
  • 00:22 the first one of those is the match function.
  • 00:25 Now the match functions purpose is to take a piece of data like sleeping bags in cell
  • 00:31 A14, look in a list, like we see in A5 to A8,
  • 00:35 and return the numeric position of the item if it finds it.
  • 00:40 So in this case sleeping bags would be the second item in this list.
  • 00:45 Before we get started with actually building the match function though,
  • 00:47 I also want to call out the fact that the data that I'm looking at right now in
  • 00:52 A5 to A8 is in an official Excel table.
  • 00:55 How can I tell?
  • 00:56 Because it says table tools design.
  • 00:59 Notice that when I click outside the table, that table tools tab goes away.
  • 01:03 When I click inside the table, it comes back.
  • 01:06 You'll also notice that the name of this table is product lines.
  • 01:09 This is important, because every table comes with, built-in,
  • 01:12 a named range that allows us to quickly go, and jump, and
  • 01:16 select all of the data that's inside the table without picking up the header.
  • 01:20 So let's build a match function now.
  • 01:23 What we're gonna do is we're gonna type in equals match.
  • 01:27 And it says, what's the value that you would like to look up?
  • 01:30 Well, we'll look for sleeping bags.
  • 01:32 And we'll say column.
  • 01:34 The next question is, where's the lookup array.
  • 01:36 In other words, where would you expect to find your sleeping bags?
  • 01:39 So we'll select the body of the table.
  • 01:41 And notice that it's not giving me a range of of A5 to A8,
  • 01:44 it's actually giving me the name of the table with the column at the end of it.
  • 01:48 And that's fine.
  • 01:49 You could also go back and use the reference A5 to A8 if you wanted to.
  • 01:53 I would recommend you lock that in as absolute with a dollar sign if you're
  • 01:57 going to do that.
  • 01:59 The next thing that we add is comma.
  • 02:02 There are three different ways that we can match data here.
  • 02:06 You know, when you look at the less than, greater than,
  • 02:09 it kind of gets a little bit confusing when you're working with text.
  • 02:11 But the key part that I want you to recognize on this is it's the very end.
  • 02:16 Look up array must be placed in ascending order.
  • 02:20 This is what we call an approximate match.
  • 02:23 What this means is it's going to look for sleeping bags, and if it can't find it,
  • 02:26 it's going to look at the list, and it will try and
  • 02:28 find the closest thing that it can to what we actually expect.
  • 02:34 That gets a little weird with alphabetical numbers, it works better when
  • 02:37 we're actually talking about numbers, but regardless, it is a match type.
  • 02:41 If you look at greater than,
  • 02:43 when we go down here, the key part here is it works the opposite way.
  • 02:47 It'll actually look to see things in descending order.
  • 02:49 So everything must be sorted in descending order with the largest letters first.
  • 02:57 In this case, I actually want an exact match, because we're going to later
  • 03:02 set up something that says you can only put in valid options.
  • 03:06 So an exact match will make sure that if somebody puts in sleeping bags,
  • 03:10 they will return the correct item for sleeping bags.
  • 03:13 And if they get something that's wrong, it won't give us a match at all.
  • 03:16 So what I'm going to do is I'm going to use the zero, and I would recommend that
  • 03:19 even though this argument is optional, you always declare it explicitly.
  • 03:23 We'll say enter, and you'll notice that sleeping bags comes back as number two.
  • 03:27 If I were to re-sort my data, so we'll go to data, and we'll sort it Z to A,
  • 03:31 you'll notice that now sleeping bags is in the third position.
  • 03:35 I'll send it back A to Z.
  • 03:38 What would happen if I change the spelling on this?
  • 03:41 I'll call it sleeping bagst.
  • 03:43 It comes back and it says #N/A.
  • 03:45 And that's because it can't find sleeping bagst at all.
  • 03:49 Now the difference is, if I were using a different match type with minus one,
  • 03:55 it would return nothing.
  • 03:57 It still can't find it.
  • 03:59 If I were to go and put in one, it says, hey, this is the second item.
  • 04:07 The reason for this is because the data is sorted in ascending order.
  • 04:15 So if you look,
  • 04:16 when we go with the one here it says the lookup must be placed in ascending order.
  • 04:19 It finds the closest thing that it can, which is sleeping bags.
  • 04:23 Now again, I'm gonna use an exact match for
  • 04:26 this because I always want to return the correct value.
  • 04:29 I want to know if something's not there.
  • 04:31 Here we go, we'll roll this back.
  • 04:34 Where approximate matches can be useful is when we're dealing with things like dates.
  • 04:39 Notice this is June 1st, notice this is June 30th.
  • 04:43 In this case I would like to find a match for June in my dates.
  • 04:52 If I go and now say, all right, well, let's go with a match that says less than.
  • 04:58 It's sorted in ascending order.
  • 05:00 If I hit enter on this it comes back and tells me five.
  • 05:02 One, two, three, four, five.
  • 05:05 Reason being is because it has not hit June 30th and
  • 05:08 therefore it's not gonna pick that up as the valid answer.
  • 05:12 If I were to go and change this to minus one,
  • 05:16 we would see it would return # N/A because the data's not sorted descending.
  • 05:20 It starts with January on the left, okay?
  • 05:22 But the key part around this is that it allows me,
  • 05:26 if I were to go with an exact match, I should just call that out as well,
  • 05:29 June 1st isn't in the list, it's June 30th, so it returns a #N/A value.
  • 05:33 So in this case, I do want to pick up something that's approximate.
  • 05:36 And that's why I would go with match of one, less than,
  • 05:38 and say alright, it returns five.
  • 05:41 Could I potentially adjust it?
  • 05:42 Yes, although I might want to look at changing the data that I actually have
  • 05:46 here to try and come up with something a little bit different.
  • 05:49 So this is how the match function works, it takes some playing with to get totally
  • 05:53 used to it, but it's a very powerful function when you start working with this.
  • 05:56 In particularly, when we combine it with other functions.

Lesson notes are only available for subscribers.

Gift this course