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
9.5 KB Other Lookup Methods - Completed.xlsx
9.9 KB Other Lookup Methods - Extra Practice.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 addition to VLOOKUP which is one of the most favored lookup functions in Excel there's a couple of other
  • 00:10 ones we've got INDEX, MATCH and HLOOKUP which is VLOOKUP's horizontal cousin.
  • 00:16 I'm going to start by taking a look at INDEX. This one's a little bit different, a little interesting.
  • 00:20 The way this works we say =INDEX and its going to ask us for
  • 00:25 an array or a two dimensional range. So this is looking for a table that we have here.
  • 00:30 And then it says ok no problem you've given me a range to look in. Tell me what row you'd like
  • 00:35 we'll look in row three and what column would you like to return?
  • 00:39 So two.
  • 00:41 So what should happen here is it should look in this table,
  • 00:45 find the third row in the second column and return the value dialysis for us. If we say ok it comes back and it does that.
  • 00:53 So this is a really handy function for working with a two dimensional array when we know what intersect we actually want to pullback
  • 01:03 but that of course can sometimes be a bit of a challenge.
  • 01:06 That's what the MATCH function is all about so in this case if I look up here and I say alright I've got my patient number in cell B3 how about I
  • 01:14 look that up and see what row is that actually in in my data table?
  • 01:19 To do that I can say =MATCH
  • 01:22 I'm going to look up the value in B3
  • 01:25 and the look up array I'm going to go and look at all the customer numbers
  • 01:31 and then I'm going to ask and it's going to say would you like, well it doesn't actually
  • 01:35 write this here but it works the same way as the VLOOKUP function. You can pretty much either supply true
  • 01:39 for a guess or false for an exact match so we're going to type in false
  • 01:44 and that will give us an exact match. It's going to look for this particular value and if it can't
  • 01:48 find it, it will return #N/A and if it can it will return the numeric position
  • 01:52 in the dataset. And this works either on horizontal or vertical. So if I say Enter
  • 01:57 it comes back and says that that's three if I look at the customers ending in 91 there's one, there's two, there's three.
  • 02:04 The implications of this are now that I can take this MATCH function, I'm going to copy that
  • 02:11 and I can actually nest it inside my INDEX function in place of the hard coded value.
  • 02:17 Paste that and hit Enter and it still comes back with dialysis because that's what the customer here with 91 was actually looking at.
  • 02:25 What would happen if I went and changed the customer number to end in say 93?
  • 02:30 Let's do that right now.
  • 02:33 And Enter.
  • 02:34 It comes back with drug addiction because
  • 02:37 the MATCH function that's nested inside here, this particular match
  • 02:41 is now looking for the value in B3 here in this area and is finding it in the fourth position
  • 02:48 and feeding that back inside which works out really nicely.
  • 02:52 INDEX and MATCH combinations are a much more powerful way of working with VLOOKUP
  • 02:58 because you can also do another match for the other side as well. So if I'm looking for say,
  • 03:05 well let's say that we wanted to match one more in here, let's say MATCH
  • 03:09 and this time I'm going to just feed in text, I'm going to match the treatment
  • 03:13 cost.
  • 03:16 And I'm going to match that across the top here and that should return me my third column here comma false.
  • 03:25 When I nest that in place it'll now pullout and say 14,000. So for customer number
  • 03:32 10456493 when I come across it's matched the fourth column to $14,000.
  • 03:37 If I went back to customer number 91
  • 03:39 it should give me the value of 22,000.
  • 03:42 Which it does so this is great because INDEX MATCH can actually be a lot faster than VLOOKUP believe it or not even with two nested
  • 03:50 MATCH functions in there. But it can allow you to dynamically move around through your
  • 03:53 range where VLOOKUP is pretty much targeted to that one column so
  • 03:57 it's a handy formula to know.
  • 04:00 The other one I want to look at really quickly here is HLOOKUP and what HLOOKUP
  • 04:04 does is it works like VLOOKUP except instead of looking in a column
  • 04:08 and returning the offsetting columns HLOOKUP looks in a row and returns offsetting rows.
  • 04:13 So apart from that it works exactly the same so we'll say =HLOOKUP
  • 04:17 and what we're going to look up right now is we'll look up treatment needed.
  • 04:24 So much the same, what do you want to lookup?
  • 04:27 Where do you want to look it up? We'll look it up in this particular table.
  • 04:31 What row would you like to return? We'll return the second row in this case and again we 're going to choose false
  • 04:37 and get an exact match and it comes back and says that row two has a bowel obstruction.
  • 04:43 If I were to knock this down to row four
  • 04:48 it'll come back with dialysis because this is row 1, 2, 3 ,and 4 here. So the header is included because it is included inside my range.

Lesson notes are only available for subscribers.

Gift this course