Other Lookup Methods

Quick reference

• 00:04 In this video, we're gonna look at three different lookup
• 00:07 functions which are similar and yet have nuanced differences to VLOOKUP.
• 00:12 The first we'll start with, which is actually gonna be very similar to VLOOKUP,
• 00:15 is the MATCH function.
• 00:16 So we'll say =MATCH, and you'll notice that we get prompted for a lookup value.
• 00:21 So we'll choose to grab B6 and
• 00:23 the lookup array, which is where do we want to look our data up?
• 00:29 We'll grab all the vacation numbers, including the header.
• 00:32 And finally, we get the option to declare a match type.
• 00:34 Now, this will accept true and false, just like VLOOKUP.
• 00:38 But it also gives you the ability to choose a less than, exact,
• 00:41 or greater than.
• 00:41 Now, I want an exact match.
• 00:43 This is the equivalent of VLOOKUP's false, so
• 00:47 we'll say OK and Enter and you'll notice it gives me a value of 7.
• 00:52 Where does that come from?
• 00:53 Vacation numbers 1, 2, 3, 4, 5, 6,
• 00:56 this is the seventh row which returns Punta Cana in the Dominican Republic.
• 01:01 Now, this function is super useful for
• 01:04 actually feeding other functions like the VLOOKUP.
• 01:07 Instead of us hard coding the column parameter, we could use it there.
• 01:11 But I wanna show you this next piece here.
• 01:14 This is called INDEX.
• 01:15 And when we actually run an INDEX function, it says, what is the array or
• 01:19 what is the range?
• 01:21 So I'm gonna grab this entire table of data.
• 01:23 And then it says what's the row number and column number.
• 01:26 So I'm gonna choose ,7,2 and we'll hit Enter.
• 01:31 And notice that it gives me Punta Cana in the Dominican Republic.
• 01:34 Why?
• 01:35 It looks at this entire two dimensional range and
• 01:37 then goes with the seventh row and the second column.
• 01:41 But the key here is that if I were to go and update something over here,
• 01:45 obviously this isn't gonna change.
• 01:47 So I'd really rather actually use a MATCH function to pick up what I'm looking for.
• 01:52 So let's go back to the match for a second and we'll copy everything here except for
• 01:57 the equals.
• 01:58 There we go.
• 02:00 And now, if I go to my Punta Cana here,
• 02:03 instead of actually using the hard-coded value of 7, I'm gonna highlight that,
• 02:07 delete it, and I'm gonna paste, just Ctrl+V, the MATCH function in place.
• 02:12 And now when I hit Enter, you'll notice it still returns Punta Cana.
• 02:16 But if I were to go and do something like this, where I said 73892 and
• 02:21 hit Enter, you'll notice that it now updates to Cabo San Lucas in Mexico.
• 02:26 And the reason being is because we're dynamically using the MATCH function to
• 02:30 feed in the numeric position of where this particular item is.
• 02:33 So that's pretty neat.
• 02:35 This is what we actually refer to as an INDEX MATCH combination, and
• 02:39 we can actually even make this bigger too.
• 02:42 Maybe we want to know that it's the all inclusive location that we're looking for.
• 02:46 So instead of using the 2 here, just in case somebody moves these things around,
• 02:50 we could actually go and feed in another match where we say I'd like to match all
• 02:56 inclusive location in this particular array of data here,
• 03:04 comma 0 for an exact match, and close the parentheses.
• 03:09 So this point now, even if someone were to go and
• 03:13 insert a new column here, this is still gonna return the appropriate piece
• 03:18 because it's now matching across these four headers here to find the right one.
• 03:24 If I had not done that, if I hadn't worked with the MATCH function,
• 03:27 it would be returning a blank because it would be returning the second position.
• 03:31 But this automatically indexes up to get everything in the right place.
• 03:35 I'm just gonna delete that because we don't need to do that, but
• 03:38 you get the idea.
• 03:40 The last function I wanna look at is HLOOKUP.
• 03:43 So what's HLOOKUP?
• 03:44 Well, it's kinda like VLOOKUP's brother, except that it looks the other way.
• 03:48 Where VLOOKUP looks down the first column and
• 03:51 then finds the matching column, HLOOKUP looks across and then down.
• 03:57 So in order to actually try and figure out what we'd be looking for
• 03:59 here, I'm gonna go and write an HLOOKUP.
• 04:04 And it says what value would you like to look up here?
• 04:06 Well, I'm gonna look up the trip cost,
• 04:14 The table that I'm gonna look it up in is this area here.
• 04:19 And for the row index number,
• 04:21 why don't we just pick up the match that we're working with?
• 04:24 And finally, because we want to make absolutely certain that we're always
• 04:27 picking up the trip cost and we're never picking up something that's not spelled
• 04:30 correctly, we're gonna go with false for our HLOOKUP.
• 04:35 And just like this, we get $1,450, which is the price for Cabo San Lucas.
• 04:41 If I go and change this now to 74016, you'll notice that my match changes.
• 04:47 So it's picking up the fifth item.
• 04:50 And now my HLOOKUP is looking for trip cost and
• 04:53 then moving down to the fifth row, which is 1,950.
• 04:56 So these are different functions.
• 04:58 HLOOKUP works the same as VLOOKUP, but works across then down instead down and
• 05:03 then across.
• 05:04 INDEX is the more powerful of the functions because you can feed it
• 05:07 a couple of different matches.
• 05:09 And this is the one that actually hardcore formula people really like to use INDEX.
• 05:14 There's a huge debate over whether INDEX MATCH is better than VLOOKUP.
• 05:17 Personally, I say whichever one works is the one that you wanna use.
• 05:20 But INDEX MATCH is a little bit more robust and can be faster, believe it or
• 05:24 not, even with multiple functions.
• 05:26 So if you really wanna get into this hardcore,
• 05:29 that's the place you'd wanna focus.

Lesson notes are only available for subscribers.