Locked lesson.

Upgrade

About this lesson

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

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Other Lookup Methods.xlsx
14.4 KB Other Lookup Methods - Completed.xlsx
14.5 KB

Quick reference

Topic

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
  • MATCH is NOT case sensitive. (Looking up dog in a table will return Dog.)

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, e.g. HLOOKUP matches are NOT case sensitive. (Looking up dog in a table will return Dog.)
Login to download
  • 00:04 In this video, we're gonna look at three other Lookup functions that
  • 00:08 are similar and yet different to VLOOKUP.
  • 00:11 The first one we're gonna explore is MATCH.
  • 00:15 And MATCH actually works very, very similar to VLOOKUP.
  • 00:18 If we actually use this we say =match, and
  • 00:21 it says, what's the value you'd like to look up?
  • 00:23 Well, let's look up this 75123.
  • 00:25 And it says, where would you like to look it up?
  • 00:29 We'll look it up in this column.
  • 00:32 And again, would you like to have an approximate match or an exact match?
  • 00:37 And we're gonna go with the exact match, which is in this case zero, but
  • 00:41 it also accepts the same thing as your VLOOKUP which is comma false to get
  • 00:46 an exact match.
  • 00:47 And what you'll find is that what MATCH does, is it returns the numeric
  • 00:52 position of this record in the data set, so if we look here,
  • 00:56 we say this is cell number one, two, three, four, five, six, and seven.
  • 01:01 There it is.
  • 01:02 It's Punta Cana in the Dominican Republic is the record we're looking at.
  • 01:06 Why this is useful is because we can actually feed a range, and pick up
  • 01:11 the numeric indicator, and that's really handy for working with our next function.
  • 01:16 See what INDEX does is it actually allows us to specify a two-dimensional range.
  • 01:22 So we'll grab all of these cells,
  • 01:24 including our headers all the way down to the bottom right hand corner.
  • 01:28 And with INDEX we can then put in and say, what row number would I like to get?
  • 01:32 So I'll say you know what, give me row number 7, and give me column number 2.
  • 01:37 And what would happen here is that it will look down seven rows, and
  • 01:41 it will look across into the second column, and
  • 01:44 it would return in Punta Cana in the Dominican Republic.
  • 01:50 So, why would we do this though?
  • 01:52 Where we hard code our values,
  • 01:55 when we can actually point this back to a match function, and say OK?
  • 02:01 Because now if I go and change this number to 73871 for
  • 02:06 example, that would return hopefully Puerto Vallarta and Mexico.
  • 02:12 When I hit Enter, it updates the MATCH.
  • 02:14 MATCH is found as the third record.
  • 02:16 We're using that in INDEX, and if you wanna shortcut this process
  • 02:21 then basically what you do is you take your MATCH function.
  • 02:24 Everything except for the equals here.
  • 02:27 And we can actually nest this in place of B5 in our INDEX function.
  • 02:32 So basically, instead of referring back to B5, which has the MATCH function, I'm just
  • 02:37 copying the guts of the MATCH function, and I'm gonna paste it directly in here.
  • 02:42 And this is what you normally see from an INDEX function.
  • 02:45 This is what we call an INDEX-MATCH combination.
  • 02:49 This is kind of a different look at things, rather than using VLOOKUP.
  • 02:53 So what's cool here is that we've provided the two dimensional range and
  • 02:58 we've got a match in here that's matching against B3.
  • 03:02 And it's pulling out the second column because that's where we told it go,
  • 03:06 although we could very easily use another match function in here.
  • 03:10 Instead of using column two, what if we said, give me a match.
  • 03:14 What would I like to match?
  • 03:15 I'd like to match the all inclusive location.
  • 03:19 So, this is the header that you see in the table here.
  • 03:23 Where would I like to match that?
  • 03:25 I'm gonna match it in these three cells here.
  • 03:29 And, would I like to guess or would I like an exact match?
  • 03:31 We'll say false.
  • 03:33 So now I'm using two match functions in my INDEX,
  • 03:36 so INDEX gives me the two-dimensional range.
  • 03:38 The first match is gonna match this against these records and
  • 03:43 return the numeric index for.
  • 03:44 The second match is gonna look across the top and match for all inclusive location.
  • 03:51 When I hit Enter, you'll see that it gives me Puerto Vallarta, Mexico.
  • 03:53 Just as I'd expect.
  • 03:55 Or if I want to change this out and say I don't really want that.
  • 03:59 Let's grab trip costs.
  • 04:01 The match function now will look at these three, and say,
  • 04:04 trip costs matches the third piece, and now I get the trip cost.
  • 04:09 Now VLOOKUP also has another cousin which is called HLOOKUP.
  • 04:14 V stands for vertical, which means it looks down the first column.
  • 04:18 H stands for horizontal, which means it looks across the table.
  • 04:22 So with HLOOKUP, we would say =HLOOKUP,
  • 04:27 and we'd say, what's the value that we wanna look up?
  • 04:30 We're gonna look up trip costs, because it's gonna look in the first row.
  • 04:34 And find our reference.
  • 04:36 And then it says, what's our table?
  • 04:38 Here is the entire table.
  • 04:40 And it says, which row would like me to pick up?
  • 04:42 We could use our match function, or we could just put a value in, let's say 6.
  • 04:47 And we can comma false because we wanna have an exact match here.
  • 04:52 At that point it gives us 1775 which is row one, two, three, four, five, and six.
  • 04:59 So same as VLOOKUP, but it works sideways.
  • 05:02 So these are some of the other lookup methods that you have at your disposal
  • 05:06 inside Excel.

Lesson notes are only available for subscribers.

Gift this course