

You’ve probably heard of VLOOKUP which is a very popular and powerful formula in Excel. Far less known is the little brother: HLOOKUP. It basically works the same way as VLOOKUP with one difference: Instead of looking up values vertically, HLOOKUP works horizontally. In this article, you learn how to use HLOOKUP, what to keep in mind, possible error messages and some more advices about HLOOKUP.
Contents
HLOOKUP searches for a value in the topmost row and returns a value from the same column.
The description text in Excel summarizes it quite well:
Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array.
That means you got a table with data and you are searching for an item in the top row. Once you’ve found the value, HLOOKUP will look below within the same column and return a value from another row below.
HLOOKUP works the same way as the VLOOKUP formula with the only difference, that the ranges are transposed: Instead of in a column, the HLOOKUP searches in a row (HLOOKUP = horizontal lookup; VLOOKUP = vertical lookup).
Structure of the HLOOKUP formula in Excel
The HLOOKUP has four parts, of which three need to be defined. The fourth part is usually just “FALSE”, so we can omit this part for now. Let’s have a look at HLOOKUP step by step.
Example of HLOOKUP
Let’s fill the definitions above with a simplified example. You want to search for “ghi” and get the number from row 3 returned.
The complete formula might look something like this: =HLOOKUP(“ghi”,1:4,3,FALSE)
Another example for the HLOOKUP formula
The image on the right hand side shows a simple usage of the HLOOKUP.
The complete formula is =HLOOKUP(B5,1:3,3,FALSE)
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
Learn more or get it on Amazon!
HLOOKUP: Get the tax rate from the table above
In this example we want to get tax rates from the table in the range C2 to H3. We want to search for the country in row 2 and return the tax rate from row 3.
Again, the HLOOKUP has four parts (example for cell D12):
The complete formula is =HLOOKUP(C12,$C$2:$H$3,2,FALSE)
There are basically three major (alleged) error messages, which actually tell you quite well how to correct your formula. You should check your formula, if you got one of the following return values:
If the above hints don’t help or there is another error message (such as “#DIV/0!”), try searching for the value manually. Maybe there is already an error in your data.
In order improve the usage of the HLOOKUP formula, it’s recommended trying the following tips and tricks:
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.