HLOOKUP function in Excel - Step-by-Step Guide

Introduction

The HLOOKUP function helps you find information in a table by looking across the top row. It is useful when your data is organized in rows and you want to find a value below a matching header.

When you have a table with months as column headers and want to find sales for a specific month.

When you want to get the price of a product listed in the first row of a table.

When you need to find a student's score by looking up their name in the top row of a grade sheet.

When your data is arranged horizontally and you want to retrieve related information from rows below.

When you want to quickly match a category name in the first row and get its details from a specific row.

Steps

Step 1: Click

- cell where you want the result

The cell is selected and ready for input

Step 2: Type

- the selected cell

The formula input starts

💡 Start with an equal sign (=) to enter a formula

Step 3: Enter the formula

- the selected cell

The formula is visible in the formula bar

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

Step 4: Replace lookup_value with the value or cell reference you want to find

- inside the formula

The formula now knows what to search for

Step 5: Replace table_array with the range of cells containing your data

- inside the formula

The formula knows where to look for the data

Step 6: Replace row_index_num with the row number in the table from which to return the value

- inside the formula

The formula knows which row's value to return

Step 7: Optionally, add TRUE or FALSE for range_lookup to specify approximate or exact match

- inside the formula

The formula will perform exact or approximate matching accordingly

Step 8: Press Enter

- the keyboard

The cell shows the value found by HLOOKUP

Before vs After

Before

Cell A1 to D1 contain headers: Jan, Feb, Mar, Apr. Rows below have sales numbers. Cell F1 is empty.

After

Cell F1 contains the sales number for Feb after entering =HLOOKUP("Feb", A1:D5, 2, FALSE).

Settings Reference

lookup_value

📍 first argument in HLOOKUP formula

The value to search for in the first row of the table

Default: none

table_array

📍 second argument in HLOOKUP formula

The range where the function looks for the lookup_value and returns data

Default: none

row_index_num

📍 third argument in HLOOKUP formula

The row number in the table_array from which to return the value

Default: none

range_lookup

📍 fourth optional argument in HLOOKUP formula

TRUE for approximate match, FALSE for exact match

Default: TRUE

Common Mistakes

Using a row_index_num that is less than 1 or greater than the number of rows in table_array

HLOOKUP cannot return a value from a row that does not exist in the selected range

Ensure row_index_num is between 1 and the number of rows in your table_array

Forgetting to set range_lookup to FALSE when an exact match is needed

HLOOKUP may return an incorrect approximate match if range_lookup is TRUE or omitted

Use FALSE as the fourth argument to get an exact match

Selecting a table_array that does not include the first row with lookup values

HLOOKUP always searches the first row of the table_array for the lookup_value

Make sure the first row of your table_array contains the values you want to look up

Summary

HLOOKUP searches for a value in the top row of a table and returns a value from a specified row below.

Use it when your data is organized horizontally with headers in the first row.

Remember to set range_lookup to FALSE for exact matches to avoid wrong results.