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.

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

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")))))

you nerd!

Hi Dan, I wondered if you could help. I look after accommodation and travel for my clients and I am looking for a formula that will generate rooming lists. I use codes for each hotel I use and I add these in the interesection between the name of the guest and the date they are staying at the hotel. This automatically updates my total per hotel and per guest but I can't work out a way of generating rooming lists easily. Is there a formula that I can use easily? Something like if excels finds a specificied code for a specified date - could it return the name of the guests? Thanks

Hi Marco. I had a little go at this and will email you the outcome. It's not overly pretty

Basically you need to create the construct for where to look. So by selecting a date, you're telling it where to start the VLOOKUP. And then you need to figure out how many rows to count across.

Let me know if you have any questions about the file I send.

Dan.