How to Use the HLOOKUP Function in Excel

Everyone who uses Excel heavily eventually learns how to use VLOOKUP (Vertical Lookup) to search for data organized in columns. However, occasionally you will receive a poorly designed spreadsheet where the data is laid out horizontally across rows, rather than vertically down columns.

When your dataset is horizontal, VLOOKUP becomes entirely useless. To search across the top row of a table and return a value from a row underneath it, you must use its horizontal counterpart: HLOOKUP.

How HLOOKUP Works

HLOOKUP stands for Horizontal Lookup. It scans the very top row of a selected table from left to right until it finds a specific search term. Once it finds the match, it drops straight down that column to retrieve a piece of data from a row you specify.

The syntax requires four pieces of information:

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

A Real-World Example

Imagine you have a financial summary where Row 1 contains the names of different months (Jan, Feb, Mar) stretching horizontally from column B to M. Row 2 contains the “Total Revenue” for those months, and Row 3 contains the “Total Expenses.”

You want to build a search box in cell A5 where you can type “Mar” and have cell B5 instantly display the Total Expenses for that month.

1. Click on cell B5 (the cell where you want the answer to appear).
2. Type the beginning of the formula:
   =HLOOKUP(
3. lookup_value: Click cell A5 (this tells Excel to search for whatever month you type here). Type a comma.
4. table_array: Highlight your entire data table, ensuring the months (the things you are searching for) are in the very top row. Let’s assume this is range B1:M3. Type a comma.
5. row_index_num: You want the “Total Expenses.” In your highlighted table, Row 1 is the Months, Row 2 is Revenue, and Row 3 is Expenses. Therefore, the expenses are in row number 3. Type the number 3, followed by a comma.
6. range_lookup: Type FALSE. This forces Excel to look for an exact match (e.g., exactly “Mar”). If you omit this, Excel might guess and return the wrong data.
7. Close the parentheses and press Enter.

The final formula should look like this: =HLOOKUP(A5, B1:M3, 3, FALSE).

Now, whenever you type a month into cell A5, the HLOOKUP formula will scan horizontally across Row 1, find the matching month, drop down to the 3rd row, and return the correct expense figure.