Locked lesson.

Upgrade

About this lesson

Why not to use HLOOKUP and VLOOKUP functions.

Quick reference

Don't Use HLOOKUP and VLOOKUP

Discover why to avoid VLOOKUP & HLOOKUP functions in a formula.

When to use

When looking up data in a table – two functions most modelers are very familiar with are VLOOKUP and HLOOKUP.  When using these functions it’s very easy to make a mistake.

Instructions

VLOOKUP has the following syntax:

  • Lookup_value: What value do you want to look up?  
  • Table_array: Where is the lookup table? 
  • Col_index_num: Which column has the value you want returned?
  • [Range_lookup]: Do you want an exact or an approximate match?  This is optional and to begin with, I am going to ignore this argument exists.

HLOOKUP is similar, but works on a row rather than a column basis (horizontal rather than vertical).

Example

  • In this above example, the formula in cell C13 seeks the value 2 in the first column of the table D5:H9 and returns the corresponding value from the fifth column of the table (returning 3,840).   
  • Problems arise if columns are added or removed from the table range  
  • With a column inserted, the formula contains hard code (5) and therefore, the eighth column (H) is still referenced, giving rise to the wrong value.   
  • Deleting a column instead is even worse: Now there are only four columns so the formula returns #REF 
  • It is possible to make the column index number dynamic using the COLUMNS function: 
    • Duplicate values in the lookup column can also create issues.  With one duplicate, the following happens: Here, the second value is returned, which might not be what is wanted.  With two duplicates the same thing happens and the third value is returned: When there are three duplicates, the value returned is the fourth of five.  The problem is, there’s no consistent logic and the formula and its result cannot be relied upon.
Login to download
  • 00:04 So far in this functions section we've covered up things that some that
  • 00:09 consider conditions and consider some of the things that meet conditions.
  • 00:15 Sometimes you want to look up things that meet conditions, and two of
  • 00:19 the functions that get used more often than any others are HLOOKUP and VLOOKUP.
  • 00:25 And I'm gonna say something very controversial now.
  • 00:28 Don't use them.
  • 00:30 Let me explain.
  • 00:31 I know it's a bit strange, really, for
  • 00:34 me to actually now give some examples of things I don't want you to use.
  • 00:38 But there's a reason for this.
  • 00:41 If you don't know these functions,
  • 00:42 then feel free to just move on to the next video.
  • 00:45 But I suspect most of you out there that's looking at this do know that HLOOKUP and
  • 00:50 VLOOKUP functions, and do use them.
  • 00:52 So, I need to convince you why you shouldn't
  • 00:54 before I move on with the functions you should use.
  • 00:57 So, there's a few functions in the lookup family.
  • 01:00 I'm not saying don't use any of them.
  • 01:03 I just wanna say know the pitfalls, and know there's better things out there.
  • 01:06 So HLOOKUP stands for horizontal lookup.
  • 01:10 VLOOKUP stands for vertical lookup.
  • 01:13 And essentially it's like looking up somebody's phone number in a phone book.
  • 01:17 You search for their name and then you look across to the right column and
  • 01:20 find their phone number and then you dial.
  • 01:22 Now it's the same thing here.
  • 01:24 Imagine the person's name is in row five of this table for HLOOKUP.
  • 01:29 And you're looking for the number two, and what I'm actually looking to do here,
  • 01:34 is actually return the value in row five.
  • 01:40 See, now most often when people model, that's how they do it,
  • 01:46 and the syntax is HLOOKUP C12, which is the number 2, in the table
  • 01:52 D52H9 highlighted in red, and then return the corresponding value in row five.
  • 01:59 Now if I hadn't actually clicked on that formula,
  • 02:02 you wouldn't know which row I'm supposed to be returning.
  • 02:04 That's part of the problem it breaches transparency, but it's worse than that.
  • 02:09 Let me show you that's horizontal lookup.
  • 02:11 It always returns values in the table.
  • 02:15 And what it's looking up has to be always
  • 02:17 in the first row of the table you've defined.
  • 02:21 VLOOKUP, similarly, same table, this time it's the first column.
  • 02:25 And what we've got here is VLOOKUP.
  • 02:29 Lookup the value 2 in column D, that's D5 to D9.
  • 02:33 The table D5 to H9.
  • 02:35 And return the fifth column.
  • 02:37 That's what it's doing.
  • 02:39 So why don't I like these things?
  • 02:41 Well, the problem is for a start, it tends to promote hard code.
  • 02:45 People put this in.
  • 02:46 At least it would be nice if there was a cell reference down here that
  • 02:49 said number five.
  • 02:49 Then it would be some form of both flexibility and transparency.
  • 02:54 But people don't tend to do that.
  • 02:55 But it gets worse.
  • 02:57 What happens if I start inserting columns, for instance, in the VLOOKUP?
  • 03:04 Now I get 48.
  • 03:05 Where does that 48 come from?
  • 03:07 Well, it found the number two.
  • 03:09 That's column one, two, three, four, five.
  • 03:11 It doesn't stick.
  • 03:13 It doesn't stick.
  • 03:15 And it gets worse than that.
  • 03:16 If I start deleting columns instead, then I get #REF,
  • 03:21 I'm getting the prima facie error I'm always trying to avoid.
  • 03:24 No, now there is a way around this.
  • 03:28 You could choose for the HLOOKUP function, you could use the ROWS function,
  • 03:37 Every rose has its thorn.
  • 03:39 And for the VLOOKUP function, I'll do my best Lord of the Rings impersonation.
  • 03:42 Golem, the COLUMNS function.
  • 03:49 And you count the number of columns.
  • 03:50 So if you wanted to always return this column,
  • 03:53 the way to do that is given it's always got to look up in this column.
  • 03:57 Instead of using the number five, you type in the columns function
  • 04:04 and we can go and highlight, let's say, these cells.
  • 04:09 Notice five columns there, press Enter, doesn't seem to have had any
  • 04:14 difference but if I actually insert, it still gives me 3840.
  • 04:18 If I go delete, it still gives me 3840.
  • 04:24 So at least it gives me some flexibility, but even then I don't like VLOOKUP.
  • 04:30 Let me explain why.
  • 04:33 If I put the number 2 in here I've got two 2s,
  • 04:37 what's the value it's going to give me when I press Enter here?
  • 04:43 It gives me 3,840.
  • 04:46 Okay, so let me change that to a 1.
  • 04:49 It gives me 5, so it's looking up the first two in the table that you can find.
  • 04:55 Okay, I see.
  • 04:56 So let's do two, and what I'll do is I put a two here, so
  • 05:01 I'd expect the value to be 29,116, and it is!
  • 05:05 Getting some idea here.
  • 05:07 Let's put one in the last one, skip the fourth one, press Enter.
  • 05:10 It's not 375,000.
  • 05:13 It's still 29,160.
  • 05:16 It's confusing, it's inconsistent.
  • 05:19 I don't like it, there's better.
  • 05:21 I'm going to introduce you to the next one,
  • 05:23 we'll start talking about index and match.
  • 05:25 Please use those instead.
  • 05:28 Don't use these.

Lesson notes are only available for subscribers.

Gift this course