VLOOKUP and HLOOKUP

VLOOKUP is arguably the most useful power-function in Excel. Time and time again, I call upon its beauty to bring back a value from a simple array of data.

But we shouldn't forget about its lesser-used sibling, HLOOKUP.

First, VLOOKUP. For those not in the know, it allows you to look for a value in a range, and bring back a value associated with that value from the range in which you're looking. So you may want to have a cell in which you select an employee's name from a dropdown and for their salary to appear in the next cell. Or you may want to bring back the stock levels for a range of stock IDs.

Below is the syntax of the VLOOKUP function:

=VLOOKUP(cell,range,number,boolean)

The cell is the thing that you want to look up. The needle, if you will. The range is the area in which you want to hunt for it. The haystack if you will. The number is the column number in the target range that you want to return. And the boolean is an indicator to tell Excel whether to only find exact matches (0) or to also return close matches (1). (In my 16 years of using Excel, I've never once set this value to 1.)

The main two rules to understand are:

  • The thing being looked up must appear in the first column of the range in which you're looking it up
  • The number is the column number containing the desired data. So if your range runs from column B to column G, setting this to 3 will bring back data in column D (column B is 1, column C is 2, column D is 3). It must be positive, so the data you return must be to the right of the column you're looking up in.

So take the following formula:

=VLOOKUP(A1,$G:$Z,4,0)

This will take the value in cell A1, look for it in column G (the first column of the range), and bring back the associated data item from column J (the fourth column of the range). If A1 appears more than once in column G, it will look solely for the first occurrence. If it doesn't appear at all, it will bring back an #N/A error.

The range doesn't have to be a full set of columns, by the way. It can instead be a square range.

So now to the HLOOKUP.

It's exactly the same, apart from rows and columns being switched.

=HLOOKUP(A1,$7:$26,4,0)

This will take the value in cell A1, look for it in row 7 (the first row of the range), and bring back the associated data item from row 10 (the fourth row of the range). If A1 appears more than once in row 7, it will look solely for the first occurrence. If it doesn't appear at all, it will bring back an #N/A error.

I find I rarely use the HLOOKUP function. But it's comforting to know it's there if I might need it. Which I did today.

My friend Will had a bunch of columns representing sequential dates, each row representing a milestone. There would be a single X appearing where those milestones were to hit. He wanted that milestone date appearing as a column next to the milestone name.

The solution involved replicating the date headings at the bottom of the range, given the rule stipulating that the data you need to return must be below the row you're looking up in. We put it in row 100.

And the solution was to look up the value X in the row in which the formula was appearing, and bring back the equivalent value in that newly-created row 100. So in row 5, we put the following formula:

=HLOOKUP("X",5:$100,101-row(A5),0)

Let's break that down.

  • X. This is the thing that is being looked up.
  • 5:$100. This may look odd. It's a reference to rows 5 to 100. The $ against the 100 will keep it fixed when the formula is copied down to subsequent rows. But by not having a $ against the 5, it will float. So copying that same formula down to row 6 will make it change to 6:$100.
  • 101-row(A5). This takes the current row number away from 101. So in this case, yields 96. You want to return the 96th row of data (in this case row 100). By making this a variable, it changes when the formula is copied to the other rows in the spreadsheet. Row 99, for example, will want to bring back the second row's data (i.e. still that in row 100)

So there you have it. VLOOKUP and its lesser-known sibling, HLOOKUP.

This entry was posted in Formulae explained and tagged , . Bookmark the permalink.

2 Responses to VLOOKUP and HLOOKUP

  1. parag joshi says:

    Hi,

    I have a excel sheet which have the data of stock market for last 13 years. I want to filter and just want data of last Thursday of every month. can any one guide me over this

    Regards

    • Dan says:

      Hi Parag,

      The formula below should work to figure out whether a date is the last Thursday in the month.

      Dan.

      =IF(WEEKDAY(A5,2)<>4,"No",IF(OR(MONTH(A5)=1,MONTH(A5)=3,MONTH(A5)=5,MONTH(A5)=7,MONTH(A5)=8,MONTH(A5)=10,MONTH(A5)=12),IF(DAY(A5)>=25,"Yes","No"),IF(OR(MONTH(A5)=4,MONTH(A5)=6,MONTH(A5)=9,MONTH(A5)=11),IF(DAY(A5)>=24,"Yes","No"),IF(MOD(YEAR(A5),4)=YEAR(A5),IF(DAY(A5)>=23,"Yes","No"),IF(DAY(A5)>=22,"Yes","No")))))

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>