How to Use the HLOOKUP Function in Excel

Do you need to look up a value in a table of data? If so, the HLOOKUP function in Excel is just what you need. The HLOOKUP function finds and retrieves a value from data in a horizontal table. In this guide, we will show you how to use the HLOOKUP function to return the value you are looking for. We will also discuss some of the different options that HLOOKUP offers, such as approximate and exact matching. Let’s get started!

What Does The HLOOKUP Function Do?

The HLOOKUP function searches for a value in the top row of a table and returns the corresponding value in the same column from a row you specify. The “H” in HLOOKUP stands for “horizontal”, and lookup values must appear in the first row of the table, moving horizontally to the right.

Where To Find It?

Option 1:

You can find the HLOOKUP function in the Formulas tab of Excel under Lookup & Reference

Option 2:

You can use Formula Builder to walk you through the formula step by step.

Option 3:

You can manually type the formula into any cell.

The Formula

=HLOOKUP (lookup_value, table_array, row_index, [range_lookup])

lookup_value (required) – The value to look up
table_array (required) – The table you are looking up from
row_index (required) – The row number to return
range_lookup (optional) – A Boolean to indicate an exact match or approximate match. Default = TRUE = approximate match. FALSE=exact match

Let’s Take a Look at Some Real-World Examples

Excel Workbook

Workbook_HLOOKUP_Function Download

Example 1 – HLOOKUP with an Exact Match

In the first example, we will use HLOOKUP to find an exact match from a table of data. We have a table with information about stores across different cities. Let’s find the monthly sales for the city of New York.

For the formula, we start with =HLOOKUP(. The lookup_value is C7 for New York. You can either enter this directly into the formula (enclosed in parentheses) or reference a cell containing the lookup. I recommend a cell reference. The table_array should be a reference to a range. Make sure that the row you want to search across is the top row of the range. The row_index is the number of rows to count down. This starts from the top row of the table_array. Finally, for the range_lookup select “FALSE” since we want an exact match.

Example of using HLOOKUP for an exact match

Your turn: Use the workbook and HLOOKUP to follow the examples in blue and finish the practice formulas highlighted in green. Hint, you will need to repivot the table.

Example 2 – HLOOKUP with an Approximate Match

In the second example, we will use HLOOKUP to find an approximate match from a table of data. We have a table with information about stores across different cities. Let’s find what city has the closest sales to $820,000.

For the formula, we start with =HLOOKUP(. The lookup_value is C8 for $820,000. You can either enter this directly into the formula (enclosed in parentheses) or reference a cell containing the lookup. I recommend a cell reference. The table_array should be a reference to a range. Make sure that the row you want to search across is the top row of the range. In this case, we need to make monthly sales the lookup row, so we have to move the cities below monthly sales to allow the formula to work.

The row_index is the number of rows to count down. This starts from the top row of the table_array. Finally, for the range_lookup select “TRUE” since we want an exact match.

Example of using HLOOKUP for an approximate match

Your turn: Use the workbook and HLOOKUP to follow the examples in blue and finish the practice formulas highlighted in green. Hint, you will need to repivot the table.

Tips and Tricks

There Might Be A Better Option

HLOOKUP is not always the best option to search for data. Check out our post on INDEX MATCH which is a great substitute for both VLOOKUP and HLOOKUP.

Don’t Look Up

HLOOKUP can’t look above the reference, it can only look down. If you get an #N/A error you may have asked HLOOKUP to search in the wrong direction.

Are You Seeing Double?

HLOOKUP will only return the first reference that it comes across. With that in mind, make sure to remove any duplicates from your data set, or consider another formula like SUMIF should the duplicates be needed.

Check Your Formatting

The HLOOKUP function is extremely sensitive. Make sure to watch out for extra spaces, numbers formatted as text, references over 255 cells, or other items that don’t match perfectly.

Let’s Recap

In this blog post, we showed you how to use the HLOOKUP function in Excel. It is a great tool for looking up values across a table of data. We showed you how to use HLOOKUP to return the value you are looking for, and we also discussed some of the different options that HLOOKUP offers.

Have any questions? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.