FormulaFix · Plain-English guides to Excel and Google Sheets formulas

How to Use the HLOOKUP Function in Excel (and Google Sheets)

1. Understanding HLOOKUP: Basics and Syntax

HLOOKUP (horizontal lookup) searches for a value in the first row of a table array and returns a value from a row you specify. It is the horizontal counterpart to VLOOKUP and works the same way in Excel and Google Sheets, so you can copy formulas between the two platforms without changes.

Syntax

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Argument Description Required
lookup_value Value to find in the first row of table_array. Yes
table_array Range that contains the data (first row is where the search occurs). Yes
row_index_num Row number (relative to the first row of table_array) from which to return a value. 1 returns a value from the first row, 2 from the second, etc. Yes
range_lookup (optional) TRUE (or omitted) for an approximate match (first row must be sorted ascending). FALSE forces an exact match. No

Note: HLOOKUP is available in all modern versions of Excel and Google Sheets; no special version requirements apply.

Quick Example

A B C D
P001 P002 P003 P004
12.99 8.50 15.75 9.99

Formula

=HLOOKUP("P003", A1:D2, 2, FALSE)

Result

15.75

How it works
1. Search – looks across A1:D1 for "P003".
2. Row selection – row_index_num = 2 tells it to return the value from the second row (C2).
3. Match type – FALSE forces an exact match.

2. Setting Up Your Data for HLOOKUP

For HLOOKUP to work reliably, arrange your data so the lookup keys are in the top row of a rectangular range.

  1. Place the lookup keys in the first row – HLOOKUP only scans that row.
  2. Keep the table rectangular – every row you want to retrieve must have the same number of columns.
  3. Avoid blank columns inside the array – blanks shift the column index; put visual spacing outside the range.
  4. Choose the match type – FALSE for exact matches (recommended) or TRUE for approximate matches on a sorted first row.
  5. Name the range (optional) – a defined name (e.g., ProductTable) makes formulas easier to read and protects them from column/row insertions.

Sample Data Set

A B C D
Item Apple Banana Cherry
Price 0.50 0.30 0.75
Stock 120 85 60

The first row (Item) holds the lookup keys; rows below hold the values you’ll return.

Formula

=HLOOKUP("Banana", A1:D3, 2, FALSE)

Result: 0.30

If you prefer a named range, define ProductTable = A1:D3 and use:

=HLOOKUP("Banana", ProductTable, 2, FALSE)

3. Using HLOOKUP to Find Data in Tables

HLOOKUP shines when your data are organized horizontally—e.g., months across the top and metrics down the side.

Example Table

Q1 Q2 Q3 Q4
Product A 1200 1500 1300 1600
Product B 900 1100 1050 1150
Product C 750 800 820 900

Goal: Retrieve Product B’s Q3 sales.

Formula

=HLOOKUP("Q3", A1:E4, 3, FALSE)

Result: 1050

Explanation – The function finds "Q3" in the first row (column C), then returns the value from the third row of the selected range (Product B).

Tips & Common Pitfalls

Issue Why it Happens Quick Fix
#N/A – value not found Lookup value isn’t in the first row, or there are extra spaces. Verify spelling, trim spaces (TRIM), or use IFERROR to supply a default.
#REF! – invalid row index row_index_num is < 1 or > number of rows in the array. Ensure the index is within range, or calculate it with MATCH.
#VALUE! – non‑numeric row index Row index is text. Convert with VALUE or reference a numeric cell.
Approximate match returning a wrong value range_lookup left TRUE (or omitted) on unsorted data. Use FALSE unless the first row is sorted ascending.
Dynamic range shifts when copying Relative references move unintentionally. Use absolute references ($A$1:$E$4) or a named range.

4. Advanced Techniques: Nesting HLOOKUP with Other Functions

Combining HLOOKUP with other functions expands its usefulness.

Goal Formula Explanation
Return a friendly value when the lookup fails =IFERROR(HLOOKUP(A2,$B$1:$F$5,3,FALSE),"Not found") IFERROR replaces #N/A with Not found.
Sum several rows under the same header =SUM(HLOOKUP("Q2",$A$1:$E$4,{2,3,4},FALSE)) The array constant {2,3,4} tells HLOOKUP to pull rows 2‑4, then SUM adds them.
Make the row index dynamic with MATCH =HLOOKUP("Q3",$A$1:$E$4,MATCH("Product B",$A$1:$A$4,0),FALSE) MATCH finds the row of "Product B" (3), so the formula still works if rows are reordered. Result: 1050.

Example: Using IFERROR with HLOOKUP

A B C D E
Item Jan Feb Mar Apr
Apples 120 135 110 130
Bananas 95 105 98 102
Cherries 150 160 155 158

Formula (placed in G2)

=IFERROR(HLOOKUP("May",$B$1:$E$4,2,FALSE),"Month not in table")

Result: Month not in table

5. FAQ

What is the difference between HLOOKUP and VLOOKUP?

HLOOKUP searches across the first row of a range and returns a value from a row below it, while VLOOKUP searches down the first column and returns a value from a column to the right. Choose the function that matches the orientation of your data.

How do I handle exact vs. approximate matches in HLOOKUP?

Set the fourth argument to FALSE for an exact match (recommended for most cases). Use TRUE or omit the argument only when the first row is sorted in ascending order and you intentionally need the nearest smaller value.

What should I do if HLOOKUP returns #N/A?

First verify that the lookup value exists in the first row and that there are no extra spaces or mismatched data types. If the value may be missing, wrap the formula in IFERROR (e.g., IFERROR(HLOOKUP(...),"Not found")) to display a custom message.

Is there a better alternative to HLOOKUP in Excel 365?

Excel 365 offers XLOOKUP, which can search horizontally or vertically, return default values, and handle exact/approximate matches without the need for a separate range_lookup argument. XLOOKUP also works with entire arrays, making it more flexible than HLOOKUP.

Bottom line: HLOOKUP is a straightforward way to retrieve data from horizontally‑structured tables. By preparing your data correctly, choosing the right match type, and optionally nesting it with functions like IFERROR or MATCH, you can make your lookups robust and easy to maintain.

Related guides