Locked lesson.

Upgrade

About this lesson

Learn to work with INDEX, MATCH and HLOOKUP, as alternatives to the VLOOKUP function.

Exercise files

Download this lesson’s related exercise files.

Other Lookup Methods.xlsx
14.9 KB Other Lookup Methods - Completed.xlsx
15.2 KB

Quick reference

Topic

Other lookup methods.

Description

Working with INDEX, MATCH and HLOOKUP, as alternatives to the VLOOKUP function.

Where/when to use the technique

INDEX and MATCH can be used in combination to create truly dynamic lookup methods, where HLOOKUP is better suited to horizontal data than VLOOKUP.

Instructions

INDEX

  • Allows you to return a value based on the numeric row and column positions within a range.
  • =INDEX(A1:C10,5,2)         Returns the value in B5 (the 5th row and 2nd column of the data set)

MATCH

  • Works similar to VLOOKUP, but returns the numeric position of the match, allowing it to be used in INDEX functions
  • The MATCH syntax (in English) is: =MATCH([what should it look for],[where is the table to look in],[guess if it can’t find a match])
  • =MATCH(“Coffee”,A1:A10,FALSE) would return the numeric position of the Coffee record in cells A1:A10.  This function could then be nested in the row parameter of the INDEX function.
  • Note that INDEX/MATCH combinations are actually faster than VLOOKUP formulas; something that can become important in very large workbooks.

HLOOKUP

  • HLOOKUP works just like VLOOKUP, only it looks across rows, then down rows, unlike VLOOKUP which looks down columns then across columns
  • HLOOKUP’s syntax (in plain English) is similar to VLOOKUP’s
  • =HLOOKUP([what should it look for],[where is the table to look in],[which row should it return a value from],[guess if it can’t find a match])
  • All the caveats related to VLOOKUP with exact and approximate matches apply to HLOOKUP as well
Login to download
  • 00:04 In this video, we're gonna look at some other lookup methods and
  • 00:07 how they're similar and yet different to vlookup.
  • 00:11 The first we'll lookup is hlookup, which is very similar to vlookup.
  • 00:15 It has all the same caveats about sorted data and all the rest of it.
  • 00:19 But instead of looking down the first column for match,
  • 00:23 it actually works across the first row of your data table, and
  • 00:27 then returns the number of records going down, so if we take a look here.
  • 00:32 If we wanted to say, let's go with equals HLOOKUP.
  • 00:36 And the value we'll look up is all inclusive location.
  • 00:39 We'll look that up in the table going this way, and
  • 00:43 what it's gonna do is it's gonna look across the first header here.
  • 00:45 We'll just press F4 to make that absolute, and I'll say you know what,
  • 00:49 let's return the third row comma false, because we want to make sure that we only
  • 00:53 return records that actually have All Inclusive Location in their header.
  • 01:00 And we'll say Enter.
  • 01:00 And it'll return Puerto Vallarta, Mexico,
  • 01:03 because it looks across the first, finds the all-inclusive location and
  • 01:06 says, this is row one, two, three, there we go, Puerto Vallarta, Mexico.
  • 01:10 So, exactly the same thing.
  • 01:12 What would you like look up?
  • 01:13 Where would would you like to look it up?
  • 01:14 Which row would you like returned in this case, and would you like me to guess if I
  • 01:18 can't find All Inclusive Location in the first header row?
  • 01:23 So, same thing, just tipped 90 degrees.
  • 01:25 Okay, now, the next function that I want to look at,
  • 01:28 actually gets used in combination here.
  • 01:30 And this is index and match.
  • 01:32 And we're going to start with the match function and
  • 01:34 I want to show you how this one works.
  • 01:36 Again, it's very similar to VLOOKUP in a way, but it takes one parameter less.
  • 01:41 What we're gonna do is we're gonna look up the vacation number here.
  • 01:43 So we're gonna type match, open brackets, look up the vacation number.
  • 01:49 So what would we like to look up, where would we like to look this up.
  • 01:53 We're going to look it up in the first column of our table.
  • 01:56 And I'm gonna include the header row here, I'll just make this absolute.
  • 02:01 And you'll notice that it doesn't ask me for an offsetting column,
  • 02:04 it says what's the match type you'd like?
  • 02:06 Well, again, I'd like to go with false, I don't want Excel to guess.
  • 02:09 I want to find the exact thing I'm looking for, or return an error.
  • 02:14 So the same as VLOOKUP and HLOOKUP in that way.
  • 02:17 When I hit Enter, VLOOKUP or rather Match, returns me seven.
  • 02:22 Why?
  • 02:23 One, two, three, four, five, six, seven, is the value that I was looking up.
  • 02:29 So it's returning me the numeric position of that match.
  • 02:32 This is a little different.
  • 02:34 Let's try this with the column name as well.
  • 02:35 We'll go with equals match.
  • 02:38 We'll look for All Inclusive Location.
  • 02:41 And we're gonna look across the headers, I'll lock those down as well.
  • 02:45 It's not totally necessary, I just prefer to do that so I can copy it later.
  • 02:49 Comma false, because I don't want to have any guesses about this,
  • 02:52 I only want to return the column that says All Inclusive Location, and
  • 02:56 it should return, you'd think, if vacation number is column one,
  • 02:59 All Inclusive Location is column two, it should return two.
  • 03:03 And lo and behold, it does.
  • 03:05 Why is match so useful?
  • 03:07 It's for working with index.
  • 03:10 And here's the thing.
  • 03:11 What index does is it actually provides our table that
  • 03:16 we declare, so if we say index, and we select the entire table here.
  • 03:23 We'll just lock that down to absolute so we can copy it around as well.
  • 03:26 When we hit comma, it says where's the data like, what row number is it in?
  • 03:31 Well if I want to find the match for 71523 I can just use comma seven.
  • 03:38 What column would I like to pick up?
  • 03:40 Well I'd like to pick up the second column.
  • 03:43 So we'll just use this one.
  • 03:44 Now, what INDEX does is it takes our range and
  • 03:47 it turns it into a two dimensional table that can be accessed basically by saying,
  • 03:51 give me the numeric cross-reference for this.
  • 03:54 It starts with row, so it says give me the seventh row, give me the second column.
  • 03:58 And there we go, it should return Punta Cana in the Dominican Republic.
  • 04:02 When we hit Enter, absolutely it does.
  • 04:04 What if I decided to change things up?
  • 04:06 What if I decided to use something like 73871.
  • 04:09 73871.
  • 04:13 The match is gonna work.
  • 04:14 It's gonna return Puerto Vallarta, because it's looking for the match.
  • 04:17 It finds it in the third row and it allows me to actually pull this back.
  • 04:21 Now advantages to INDEX and MATCH.
  • 04:23 Believe it or not, you can take these functions here,
  • 04:26 I'm gonna grab this guy right here, Command + C and copy it.
  • 04:30 I don't need to use this in a separate cell.
  • 04:33 I could just nest this function together.
  • 04:36 It makes a really long function.
  • 04:38 Okay, I'm pasting that in place of the cell reference I've used before, and
  • 04:41 it still works.
  • 04:43 Despite doing this with both functions, believe it or
  • 04:47 not, INDEX MATCH is way faster than VLOOKUP when
  • 04:52 you actually start using it on a regular basis to actually work with your data.
  • 04:57 It's a much better performing function.
  • 05:00 It also allows you to lookup values in areas that are other than the first
  • 05:04 column and actually return the offsetting match, which is also very, very useful.
  • 05:09 One other thing I wanna show you here quickly is I can now flip this as well to
  • 05:12 return the trip cost.
  • 05:13 And this is important because I'm gonna type in trip cost all lower case.
  • 05:18 You'll notice that it comes back with a trip for 73871 and
  • 05:21 it actually does return 1,300.
  • 05:23 So these match functions, like VLOOKUP, like HLOOKUP, are not case sensitive.
  • 05:28 And that's also very important to understand.
  • 05:30 So, there's some more variety of functions that you can use for
  • 05:33 looking up data in Excel.

Lesson notes are only available for subscribers.

Gift this course