MATCH

Quick reference

• 00:04 So we've covered off INDEX.
• 00:06 It's time to move on to its partner in crime,
• 00:08 MATCH, as the alternatives to VLOOKUPs and HLOOKUPs of this world.
• 00:13 Yes, strike a light.
• 00:15 Time to cover off this great function,
• 00:18 which returns the relative position of an item in a vector.
• 00:22 That matches a specified value sometimes in a specified order.
• 00:26 That's why that's in brackets, but it's a vector.
• 00:29 And I'm stressing this because if you look at the Microsoft's Excel help,
• 00:33 it actually says it can be in an array.
• 00:35 An array, don't forget, is more than one row and more than one column.
• 00:39 It's not true, it only works on a row of data or a column of data, i.e., a vector.
• 00:45 So the correct syntax is therefore MATCH(lookup_value,lookup_vector,match_t-
• 00:52 ype) where lookup_value is what you're looking for.
• 00:56 Look_up vector is where you're looking for it.
• 00:59 And match_type is the way you're doing the search, and it's got the values 1,
• 01:04 0 or -1.
• 01:05 So what does that have the effect of doing?
• 01:08 Well, 1's the default, if you actually pick MATCH type 1,
• 01:11 this means the data has to be what we call strict ascending order.
• 01:16 That means no duplicates and it's got to be increasing.
• 01:18 So it's minus five, minus four, minus three, blah, blah, blah, blah blah.
• 01:23 Then A to Z, then FALSE, then TRUE.
• 01:26 But one you're gonna want all the time is match_type 0.
• 01:30 This finds the first exact match, the end.
• 01:33 Nothing else needs to be specified.
• 01:34 But if it can't find it, it will return a #N/A error, for not available.
• 01:38 If match_type's -1, it's like the opposite of match_type 1.
• 01:43 This means the data has to be in strict descending order.
• 01:47 So, no duplication and it just goes the other way to match_type 1.
• 01:50 Note though, if match_type is omitted, it is assumed to be 1.
• 01:54 So be careful, because that's not the one you usually want.
• 01:58 Let me show you.
• 02:01 So let's take a look at MATCH, then.
• 02:03 It's pretty straightforward.
• 02:04 It's looking up a value in a list, and it's gonna tell you the position.
• 02:09 Let me show you how you would use it normally.
• 02:11 You would just type in here.
• 02:13 So I'm gonna go to this cell, =MATCH.
• 02:15 What's the value I'm looking for?
• 02:18 I'm looking for the number 1 in this list.
• 02:22 And then what you would do afterwards without even thinking you do ,0 and
• 02:25 just press Enter.
• 02:28 And it tells me that the first number 1 is in position 2.
• 02:31 Yes, there's lots of them, there's one number 1 there.
• 02:33 There's three more over here, and we've got errors and all sorts.
• 02:36 But it's found the first one, and that can be useful for
• 02:39 what you're trying to hunt out at somewhere else.
• 02:42 And that's it.
• 02:43 It's only going to work for a vector.
• 02:46 It doesn't work for an array.
• 02:47 So when you look at the help, it says lookup array here.
• 02:49 That's wrong, it's a vector, okay?
• 02:53 Now, let me explain you have to put the 0 in.
• 02:56 So let's go with the default, match_type 1.
• 03:00 Here, I've got my data in strict ascending order.
• 03:04 This is how it has to be if you want this to work.
• 03:07 Now look, I'm looking for the value 11.
• 03:09 Well, I've only got even numbers here.
• 03:12 What match_type 1 and -1, for that matter, will do is find approximate matches.
• 03:19 They'll find the largest value, in this case, match_type 1, I should say,
• 03:24 it will find the largest value less than or equal to what you're looking for.
• 03:29 So =MATCH( this value in this list,
• 03:35 comma 1, close brackets, 5.
• 03:40 1, 2, 3, 4, 5, quite correct,
• 03:42 10 is the largest number in that list that's less than or equal to 11.
• 03:47 That's it.
• 03:49 If I take out the number 1, It still works, because that's the default.
• 03:58 The one you want though, is match_type 0.
• 04:00 I've just done this, so I'll just put it in again just for completeness.
• 04:03 MATCH, brackets, find me the first number one in this list, comma 0.
• 04:09 Now if you try and put something in here that doesn't exist,
• 04:14 like a 22, use your #N/A, because it can't find it.
• 04:19 So just watch out for that.
• 04:21 Similarly, for match_type 1, if there is no value that meets the criteria,
• 04:27 that is the largest number less or equal to what you're looking for.
• 04:30 So if I put a number 1 in here for instance, you will get #N/A again.
• 04:35 So just watch out for that.
• 04:38 Finally, match_type -1 has to be in strict descending order.
• 04:42 So again, I've got no number 11, so it's gonna be looking for
• 04:45 the smallest value greater than or equal to what we're looking for.
• 04:49 So the smallest value in this list, greater than or equal to what we're
• 04:54 looking for is actually the number 12, which is in position six.
• 04:58 =MATCH( find me the value 11
• 05:02 in this list, comma -1.
• 05:08 There you go, position six.
• 05:09 And that's it.
• 05:11 To be honest, you don't really need to worry about match_type 1 and
• 05:14 match_type -1.
• 05:15 Unless you're looking up things like postal rates and things like that for
• 05:18 a model, you're building it up that way,
• 05:20 where something's between one range and another.
• 05:22 So if a parcel is less than six kilograms, it costs this.
• 05:25 If it's less than eight kilograms, it costs this and so on.
• 05:27 It's not how you're going to use it.
• 05:29 You're going to be looking for
• 05:30 an exact match, and therefore you're going to need match_type 0.
• 05:33 And that requires specifying the 0 and always making,
• 05:37 in every single case, it's a vector, not an array.

Lesson notes are only available for subscribers.