HLOOKUP function in Excel: Everything you need to know! (2024)

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 learnhow to use HLOOKUP, what to keep in mind, possible error messages and some more advices about HLOOKUP.

Before we start with the HLOOKUP: Do you know XLOOKUP? It’s new in Excel and has some advantages towards HLOOKUP. That said: Let’s get started!

What do I need HLOOKUPfor?

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 thevalue, HLOOKUPwill look below withinthe same columnand return a value from another rowbelow.

HLOOKUPworks the same way as the VLOOKUP formulawith the only difference, that the ranges are transposed: Instead of in a column, the HLOOKUPsearches in a row (HLOOKUP = horizontal lookup; VLOOKUP= vertical lookup).

Structure of HLOOKUP

The HLOOKUPhas four parts, of which threeneed to be defined. The fourth part is usually just “FALSE”, so wecan omit this part for now. Let’s have a look at HLOOKUPstep by step.

1. The first part contains the lookup value – the value you are searching for.
2. The second part defines the area you search in. Important: This area must include both rows:
   • The search rowin which you want to find your search value. This must be the toprow.
   • The return value. Please make sure, your search area is large enough.
3. Now you have to count: Starting from your search row (that means the top row),you count the rows toyour return valuerow.
4. The fourth part is (for now) always “FALSE”.

Let’s fill the definitions above with a simplified example. Youwant to search for “ghi” and get the number from row3 returned.

1. As you search for “ghi” the first part is “ghi”.
2. A little bit more difficult: The search area. The area must include the search rowand the return row. In our case that’s row1 (the header row) to row3.
3. In which rowis your return value located? It’s the third row(counting from the search row – the header row). So you just fill in 3.
4. The fourth part is “FALSE”.

The complete formula might look something like this: =HLOOKUP(“ghi”,1:4,3,FALSE)

Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Learn more

Download Free Trial

Add more than 120 great features to Excel!

Another example for the HLOOKUP formula

The imageon the right hand side shows a simple usage of the HLOOKUP.

1. First part: The lookup value you are searching for. In this case, it refers to cell B5. Cell B5contains the date 04/01/17so that means we are searching for date 4th of January 2017.
2. Second part: The range, in whichyou wantto search in. In our example, it’s the table on top. Excel searches in the topmost rowof this range. Besides the search row, the rowwith the return value must also be included in this range.
3. Third part: The number of the row, which you want to getreturned. So once Excel found the date”04/01/17″ in the cells row 1, you have to define, which value you want it to getreturned. In this example, youwant to know if you go running or to the gym, which is written in column 2 or 3 respectively. As row3 is the thirdrow after 1(whichyousearch in), youhave to write “3”. Keep in mind that the search rowis always “1”.
4. Fourth part: One optional value, which you can always consider to be “FALSE” (it determines if you want to search for the exact value).

The complete formula is =HLOOKUP(B5,1:3,3,FALSE)

One moreexample for the HLOOKUP formula

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

• What do we search for? In cell D12 we want to get the tax rate for the “UK”. This value is given in cell C12. So the first part is “C12”.
• Where do we search? We search in row 2. As this range also has to cover the return row (in our case row 3) we could just write the rows 2:3. But in our example, we further specify column C to H, so that this part is “$C$2:$H$3”.
  Please note: The dollar signs fix the range. If we copy and paste the formula, this range won’t change.
• We want to return in value from the second row (the first row is the search row, row 2). That means the value for the third part of the HLOOKUP formula is “2”.
• As usual, the last part is “FALSE”.

The complete formula is =HLOOKUP(C12,$C$2:$H$3,2,FALSE)

HLOOKUPerror messages

There are basically three major (alleged) error messages, which actually tell you quite well how to correctyour formula.You should check your formula, if you got one of the followingreturn values:

• #REF:Usually, your lookup range is not large enough. Make sure, that both your search and return rowsare within this range.
• #N/A:The value you search for is not available in your data.You could try searching it manually.
• #NAME: Check, if you misspelled “HLOOKUP”.
• 0: This is probably not an error message, but could be the real return value. For example, when your return cell is left blank, a “0” will be shown as the return value of theHLOOKUP formula.

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.

Tips & tricks for the HLOOKUP formula

In orderimprove the usage of the HLOOKUP formula, it’srecommended trying the following tips and tricks:

• In the third part of the formula, you have to determine the search range. Often, you’d copy and paste the formula so that it makes sense (whenever possible) to select whole rowsinstead of only small ranges. That makes the formula more stable.
• Also keep in mind that HLOOKUPonly returns the first search result. If your search value occurs several times in your data, only its first occurrence will be returned.
• If you want to search for a combination of different criteria, there is no direct way with the HLOOKUP. One workaround could be by generating a new “primary key”. Therefore, concatenate the different search values in a new (topmost) row.
• There are usually alternativeways instead of using HLOOKUP. Please takea look at ourarticle about when to use VLOOKUP, SUMIFSor INDEX/MATCH.