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 - Begin.xlsx
25.4 KB Other Lookup Methods - Complete.xlsx
25.4 KB

Quick reference

Other Lookup Methods

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

When to use

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 and 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 So we've looked at a couple of different ways that the VLOOKUP works.
  • 00:07 Now, what I'd like to do is explore a couple of the other lookup functions
  • 00:11 that you may run into when you actually inherit workbooks from somebody else.
  • 00:15 What we're looking at here is a list of all-inclusive destination locations with
  • 00:20 some trip costs and vacation numbers.
  • 00:21 And what I want to do is be able to look these up and
  • 00:24 actually return some values from my table.
  • 00:26 Now, I have a VLOOKUP that's showing up here that is going to look things up and
  • 00:30 return not found if it can't.
  • 00:31 And the purpose of this is really just to show you how things react similarly or
  • 00:36 differently.
  • 00:36 So, I'm going to start by using a MATCH function,
  • 00:39 which actually looks very similar to VLOOKUP in a lot of ways.
  • 00:43 What do you want to look up?
  • 00:44 Is the first question.
  • 00:45 So we're going to look up this particular vacation number.
  • 00:48 It asks for a LOOKUP array.
  • 00:49 Where am I looking?
  • 00:50 So I'm going to go and select the entire first column in this case, and
  • 00:55 then it comes back and ask for a MATCH type.
  • 00:57 And you will see that there are three options here.
  • 00:59 One is if I can't find a value, return less than, so the earlier number.
  • 01:05 Look for an exact match, or look for
  • 01:07 a value that's greater than if I can't find an exact match.
  • 01:11 So this is kind of like the true false but with a little bit more nuance.
  • 01:14 I'm going to go with an exact match, close and Enter.
  • 01:18 And this returns 7.
  • 01:20 And if you look we've got one is the vacation number two,
  • 01:25 three, four, five, six and seven is 75123.
  • 01:29 Now, if I go and pick up 75124, it returns # and A,
  • 01:33 which I can always wrap in an IFNA statement.
  • 01:37 But let's go back to what was actually working here.
  • 01:39 The next function that I want to show you is INDEX.
  • 01:42 And INDEX is also a lookup function which basically takes your array and
  • 01:46 allows you to pinpoint from an X, Y location what you're going to get.
  • 01:50 So we're going to go with INDEX here.
  • 01:52 And the first thing it says is give me an array.
  • 01:55 That's essentially a table of data.
  • 01:57 So we'll select the entire thing.
  • 01:59 And then it says, what row would you like to pick up?
  • 02:02 Well, you know what, I'm going to pick up the seventh row from my match.
  • 02:06 And then it says, which column would you like to get?
  • 02:08 Similar to what VLOOKUP would do?
  • 02:11 So I'm going to choose to go with the all-inclusive location column,
  • 02:14 which is column number two.
  • 02:15 All right, so basically this just divides it up into X, Y coordinates and says,
  • 02:19 what's your x, and what's your y?
  • 02:21 And when we hit Enter, you'll see that we get Punta Cana in the Dominican Republic.
  • 02:25 So that's pretty cool.
  • 02:27 But the neat thing about this is that we can actually put the MATCH function
  • 02:32 together with the INDEX.
  • 02:34 So what I'm going to do is I'm going to to grab this match right now, Ctrl C,
  • 02:39 and I'm going to go and put it right in place of B9, okay?
  • 02:43 So we're going to put it right in there.
  • 02:44 So this is an INDEX MATCH combination.
  • 02:48 And boom, there we go.
  • 02:49 It returns the same thing.
  • 02:51 If I go and change this number out to something different,
  • 02:54 you'll see that we get Puerto Vallarta.
  • 02:57 Where is this really useful?
  • 02:59 Well, it actually comes down to what's happening on the final piece here.
  • 03:01 So instead of using a named or number argument of number two, what I'm going to
  • 03:06 do is I'm going to add another match for my INDEX to actually match my y location.
  • 03:11 So the LOOKUP value that I'm going to be looking for here is all-inclusive,
  • 03:17 and hopefully I can spell this right, location.
  • 03:20 Where am I going to look that up?
  • 03:22 I'm going to look it up in this area up the top here.
  • 03:25 And what kind of match?
  • 03:27 Again, I'm going to go with exact.
  • 03:28 I'm going to close my parenthesis on this.
  • 03:31 Make sure that everything looks good, and I'm going to to hit Enter.
  • 03:34 And it comes back with Puerto Vallarta.
  • 03:36 Okay, great, why would I do this, instead of just using a VLOOKUP?
  • 03:41 Well, the answer is, if I go insert, a new column, notice that my VLOOKUP, breaks.
  • 03:48 And yet my INDEX does not, why?
  • 03:51 Well, because my VLOOKUP is referring to column two, and
  • 03:55 the table is expanded, but column two is blank.
  • 03:59 Now, could I put a MATCH function in here?
  • 04:01 I absolutely could, right?
  • 04:02 Just the same as what I did in INDEX, but that's a big difference.
  • 04:05 So you'll see a lot of people who will actually tell you that they don't
  • 04:08 like VLOOKUP.
  • 04:08 They'd rather use INDEX MATCH and use the INDEX MATCH,
  • 04:12 MATCH combination because it makes it a little bit more dynamic.
  • 04:16 Now, there's one more little piece here as well that we can work with is HLOOKUP.
  • 04:20 VLOOKUP tends to look down the first column and
  • 04:23 then find a match across certain pieces.
  • 04:26 There are times when you may need to look across and then find something going down.
  • 04:31 And that's what HLOOKUP is actually about here.
  • 04:34 So with HLOOKUP, we could go and say let's do an HLOOKUP.
  • 04:38 What's the LOOKUP value that I'm actually after here?
  • 04:40 Well, I think I'm going to go and look up, let me see.
  • 04:43 Let's look up, trip cost.
  • 04:46 Where's the table or array that we're going to look it up in?
  • 04:49 We're going to look it up in this entire table of array here.
  • 04:52 And what is the row index number we're after?
  • 04:55 Well, I'm going to pick up the match that I got from my vacation number here
  • 04:59 which is 3.
  • 05:00 And again I'm going to go and
  • 05:01 do an exact match on this because I want to make sure I always get trip cost.
  • 05:05 And we're going to hit Enter, and we get 1,300.
  • 05:08 The big difference, VLOOKUP looks down and then across,
  • 05:12 HLOOKUP looks across and then down.
  • 05:14 So that's a few different LOOKUP methods that are available in Excel that you
  • 05:19 may run into in the real world.

Lesson notes are only available for subscribers.

Gift this course