Locked lesson.

Upgrade

About this lesson

Let's learn the basic uses of VLOOKUP and HLOOKUP by building your own invoice template.

Exercise files

Download this lesson’s exercise file.

Invoice template.xlsx
23.8 KB

Quick reference

VLOOKUP & HLOOKUP Functions

Learn the basic uses of VLOOKUP and HLOOKUP

When to use

These functions are useful to use a value in one dataset to find a similar value in another set and return a related value.

Instructions

  • VLOOKUP/HLOOKUP(lookup value, table array, column index number, [match])
  • VLOOKUP/HLOOKUP(What, where, what to return, exact or approximate)
  • [ ] means the argument is optional, remember the default is approximate
  • The table array for VLOOKUP should be arranged in columns so that the search can take place in the left most column and the returned value to the right of that
  • The table array for HLOOKUP should be arranged in rows so that the search can take place in the top row and the returned value from the column below
  • Note the use of $$ to make copying the formula more effective
Login to download
  • 00:04 In this example, I have an invoice template that I want to set up.
  • 00:08 The idea is that I choose a client code from a list,
  • 00:13 I choose an item code from a list.
  • 00:16 And then I type in the quantity that the customer wants to buy.
  • 00:19 I would love it if Excel could fill in the rest for me.
  • 00:24 Something like this is possible with a lookup function, and
  • 00:27 there's quite a few different lookup functions in Excel.
  • 00:30 In this lesson, we'll look at Vlookup and Hlookup.
  • 00:35 V is for vertical, and H is for horizontal.
  • 00:40 What that means is if the value I'm looking for is in a column,
  • 00:43 then we'll use a vertical lookup or Vlookup.
  • 00:46 And if the value I'm looking for sits in a row, we'll use the horizontal lookup or
  • 00:51 Hlookup.
  • 00:52 Okay, so what will a Vlookup do?
  • 00:54 Vlookup will look for this value in that column.
  • 00:58 If it finds it, I can return any of the values related to it.
  • 01:02 And this is how it works.
  • 01:04 Vlookup and my value that I want to find, so what am I looking for?
  • 01:09 The next argument is the table_array.
  • 01:13 Where am I going to look for that value?
  • 01:15 Vlookup is programmed to look for the lookup value in the first column or
  • 01:20 the leftmost column of the table_array.
  • 01:23 It cannot find a lookup value in column two, or three, or after that.
  • 01:28 It can return values from any of those columns, but
  • 01:32 it can only search in column one.
  • 01:35 So type in a comma, and my next argument is the column index number.
  • 01:39 That tells Vlookup where the value is that I want returned.
  • 01:44 I want the description, which would be in the second column, so we type in a 2.
  • 01:50 The last argument is optional.
  • 01:57 If I leave it out, it'll perform an approximate match by default.
  • 01:58 But I don't want an approximate match here, I need an exact match.
  • 02:02 An exact match means it will look for
  • 02:04 the item code that exactly matches the lookup value, and nothing else.
  • 02:09 That's what I want.
  • 02:11 So we choose false here, and then we hit Enter.
  • 02:14 Sure enough, item code 344534 returns the Electric Guitar description.
  • 02:21 That is correct.
  • 02:23 Let's see if we can do this for the price per unit.
  • 02:26 Vlookup, my item code in the table array to return value in column one, two, three.
  • 02:34 We want an exact match, so false again.
  • 02:38 And I've got my price of $887.
  • 02:41 To copy this down and not get any not applicable value error messages,
  • 02:47 we should add an if error message.
  • 02:50 But let's leave that for another video.
  • 02:51 So that was Vlookup.
  • 02:53 Now let's try out Hlookup.
  • 02:56 I want to complete this billing information based on the client code.
  • 02:59 So I'm going to use an Hlookup because my lookup value is in a row,
  • 03:04 it's listed horizontally.
  • 03:06 Hlookup, what am I looking for?
  • 03:11 I'm looking for the value of H7 in this table_array.
  • 03:14 Excel is programmed to look for the lookup value in the top row.
  • 03:18 And if it finds it, which row index do I want as the output?
  • 03:23 That'll be the second one, so we type in a 2.
  • 03:26 And then again, we choose false to get an exact match.
  • 03:30 And it returns Jamming as the client name.
  • 03:35 Now to copy this formula down, I'm going to need to lock the reference to H7,
  • 03:40 and then lock the reference to the table.
  • 03:44 We can do that by hitting F4, or Cmd+T on a Mac.
  • 03:48 Then we can copy and paste this down.
  • 03:51 But see the problem?
  • 03:55 I keep on getting Jamming as the output, why?
  • 03:58 Because I've hard coded the row index number,
  • 04:01 which is where we want to get the value to return.
  • 04:04 I've got a 2 in there.
  • 04:05 But in this case, for the contact name, I need a 3.
  • 04:09 So we change that.
  • 04:11 And we could copy the formula down, but in each instance, I would need to
  • 04:16 change that row index number to a 4, then a 5, then a 6, then a 7, and so on.
  • 04:21 Let me show you a trick which will come in handy for some of your lookups maybe.
  • 04:26 I'm going to add a row number.
  • 04:28 Instead of hard coding that row index number in each formula,
  • 04:31 we can just reference the row number.
  • 04:33 And it will change as I copy the formula down.
  • 04:36 Don't lock this reference, because as I copy it down,
  • 04:39 I want to refer to the next value.
  • 04:41 So I copy this and paste it all the way down.
  • 04:46 And now my values are nice and neat, they look great.
  • 04:49 If those numbers bother you, you can change the font to white.
  • 04:54 And it's out of sight, you won't see it.
  • 04:56 So that's what a Vlookup and an Hlookup does, and that's how they work.

Lesson notes are only available for subscribers.

Gift this course