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,

I want to know how to match the value in column A with a value in column D (if there is a match); take the corresponding description of column D, which is listed in column C, and transfer it appropriately to column B.

For example:

A B C D

123 Green 999

456 Blue 321

789 Red 987

987 Yellow 888

654 Brown 555

321 Black 123

Once 321 in column A is matched with 321 in column D, it transfers Blue to the last row in column B.

I hope this makes sense and you can help me. The closest I have come is:

=IF(ISNUMBER(MATCH(A1,D:D,0)),C:C,"")

But it copies the color from C to B, adjacently, so it would find 321 in both columns and transfer black over to the last row in column B.

Please HELP!

Ashley

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.

Hi Dan,

I am trying to create a function whereby users of my spreadsheet can type in 1-3 words to search a range of parts descriptions (text entries) and pull up their associated parts numbers and descriptions.

The intent is that they don't know the part number to order or the exact description, so they enter up to three words and it returns all the part numbers with those three words (or some of those words) and the descriptions of thos parts. Hopefully that makes sense.

I don't want it to be case or context sensitive. Is there a way to do this in excel?

Thanks,

Sam

Hi Sam,

I've sent a solution to you by email. Best,

Dan.