📊 Excel Basics #20 – HLOOKUP() Function

The HLOOKUP() function searches for a value in the first row of a table and returns a value from a specified row in the same column.

Note: HLOOKUP() is less commonly used than VLOOKUP(), but it's still useful when your data is organized horizontally.

📌 What is the HLOOKUP() Function?

HLOOKUP stands for Horizontal Lookup.

It searches horizontally across the first row and returns a value from the specified row.

Syntax:

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

📌 Arguments Explained

• lookup_value → The value you want to search for

• table_array → The data table

• row_index_num → The row number to return the value from

• range_lookup

• FALSE → Exact match (recommended)

• TRUE → Approximate match

📌 Example

Jan Feb Mar Apr

Sales 50000 60000 55000 70000

Profit 8000 10000 9000 12000

To find the Profit for March:

Formula:

=HLOOKUP("Mar",A1:E3,3,FALSE)

Result: 9000

📌 Using a Cell Reference

If cell G2 contains the month name:

=HLOOKUP(G2,A1:E3,3,FALSE)

Changing the month in G2 automatically returns the corresponding profit.

📌 Common Errors

❌ Searching in a row other than the first row

❌ Using an incorrect row index number

❌ Forgetting to use FALSE for an exact match

❌ Getting #N/A when the lookup value doesn't exist

Handle errors using:

=IFERROR(HLOOKUP(G2,A1:E3,3,FALSE),"Not Found")

📌 Limitations of HLOOKUP()

• Searches only in the first row

• Works only from top to bottom

• Less flexible than INDEX()/MATCH() or XLOOKUP()

• Rarely used because most Excel datasets are arranged vertically

📌 Real-World Uses

• Retrieve monthly sales or profit from summary tables

• Find quarterly performance

• Fetch values from horizontally structured reports

• Build financial summary dashboards

✅ Best Practices

• Use FALSE for exact matches

• Ensure the lookup value is in the first row

• Combine HLOOKUP() with IFERROR() for user-friendly reports

• Prefer XLOOKUP() for modern Excel workbooks, as it supports both vertical and horizontal lookups

Although HLOOKUP() is less common today, understanding it will help you work with legacy Excel files and prepare for interviews.

Double Tap ❤️ For More