HLOOKUP Examples for Beginners in Excel

HLOOKUP searches across the top row of a table and returns a value from a specified row beneath the match. For an exact match in Excel, use =HLOOKUP("P-103",A1:E3,2,FALSE); if the product codes are in A1:E1 and prices are in A2:E2, this returns the price under P-103.

What HLOOKUP Does

The name HLOOKUP means horizontal lookup. It is useful when the values you want to search—such as product codes, month names, or score thresholds—are arranged from left to right across the first row of a range. HLOOKUP finds a value in that top row, then returns a value from another row in the same column.

Its four arguments have distinct jobs: the value to find; the full table range to search; the number of the row to return from; and whether to use an exact or approximate match. The row number is counted from the top of the selected range, not from the worksheet’s row labels. For example, row number 2 means the second row inside the range, even if that range begins on worksheet row 10.

HLOOKUP searches only the first row of the range. It does not search every row, and it does not return a result from a column on the left of the match. If your lookup values are arranged vertically in a first column instead, VLOOKUP or another lookup function may be a better fit.

Example 1: Find a Price by Product Code

Suppose a small price list is arranged horizontally in A1:E3. The first row contains P-101, P-102, P-103, and P-104, with a heading in A1 if you choose to include one; the price row below contains 12.50, 18.00, 9.75, and 25.00; and a third row contains stock counts of 8, 6, 11, and 14. To keep the example range straightforward, place the four codes in A1:D1, prices in A2:D2, and counts in A3:D3. With those values in A1:D3, this formula returns 9.75:

With product codes in A1:D1 and prices in A2:D2, the formula below looks for P-103 in the first row and returns the second row of the selected range.

=HLOOKUP("P-103",A1:D3,2,FALSE)

The range includes all rows that may supply a result. The number 2 identifies the price row: the codes are the first row of A1:D3 and the prices are the second. FALSE asks Excel to find the same code, rather than a nearby value. Including the stock row in the range does not affect this result; it simply makes that third row available to other HLOOKUP formulas.

You can make the lookup value editable by typing a code in G1. With product codes in A1:D1, prices in A2:D2, and the requested code in G1, this formula returns 25.00 when G1 contains P-104:

=HLOOKUP(G1,A1:D3,2,FALSE)

This pattern is useful for a small lookup panel: change G1 to another listed code and the formula returns the corresponding price. The formula does not alter the data; it reads the lookup value from G1 and searches the first row of the specified range.

Example 2: Return a Different Row

To return the stock count instead of the price, keep the same table and change the row number from 2 to 3. With product codes in A1:D1, prices in A2:D2, stock counts in A3:D3, and P-104 in G1, the result is 14:

=HLOOKUP(G1,A1:D3,3,FALSE)

The third argument is a position within the table array, not a worksheet row number. If your selected table were A5:D7, the stock counts would still be row 3 of that range, even though they appear on worksheet row 7. This distinction is a frequent source of unexpected results.

For readability, make the table range cover the lookup row and every possible return row, but avoid including unrelated rows. If you insert a new row into the middle of a table, check formulas that use numeric row indexes: their intended return row can shift, depending on how the range and formula change.

Example 3: Approximate Match for Grade Bands

Approximate matching is useful when the top row contains sorted breakpoints rather than unique labels. Consider a two-row grading table: the first row contains 0, 60, 70, 80, and 90; the second contains F, D, C, B, and A. With those cutoffs in A1:E1 and grades in A2:E2, a score of 76 returns C:

With score thresholds in A1:E1 and their corresponding grades in A2:E2, the formula uses approximate matching. The threshold row must be sorted in ascending order.

=HLOOKUP(76,A1:E2,2,TRUE)

With TRUE, Excel returns the value associated with the largest threshold that is less than or equal to the lookup value. For 76, the applicable threshold is 70, so the grade is C. This formula assumes the grading rules really use those boundaries; the spreadsheet should reflect the policy you intend to apply.

Approximate match is not a safe substitute for exact match when the first row contains product codes, names, or other categories. If an exact match is required, specify FALSE. If you use approximate matching, sort the first row from smallest to largest and understand what should happen below the smallest threshold. A value below the first breakpoint usually produces #N/A because no qualifying threshold exists.

Using Wildcards and Handling Missing Values

In an exact-match lookup, Excel can use wildcards for text. An asterisk stands for any number of characters, a question mark stands for one character, and a tilde makes the next wildcard character literal. This can help when a label starts with a known word, but it can also match more than one heading. HLOOKUP returns the first matching heading it encounters from left to right.

With headings North Depot, North Store, and South Depot in A1:C1 and their corresponding manager names in A2:C2, this formula returns the manager under the first heading beginning with North:

=HLOOKUP("North*",A1:C2,2,FALSE)

That result depends on the order of the headings. If the distinction between North Depot and North Store matters, use a complete heading as the lookup value instead of a wildcard pattern.

If an exact lookup might not find a code, IFNA can display a clearer message than the default #N/A error. With codes in A1:D1, prices in A2:D2, and a requested code in G1, this formula displays Not found when G1 is not present:

=IFNA(HLOOKUP(G1,A1:D3,2,FALSE),"Not found")

IFNA catches the #N/A result specifically. It does not hide every possible formula problem, which is often helpful: an invalid row index or a damaged reference should be investigated rather than silently presented as a missing code.

Common HLOOKUP Problems

Wrong return row: The row index counts within the selected range. Recheck whether the desired answer is row 2, row 3, or another row of that range.
Unexpected #N/A: With FALSE, the lookup value must match a heading. Check for extra spaces, spelling differences, numbers stored as text, or a lookup value that is absent. HLOOKUP is not case-sensitive.
Unexpected approximate result: TRUE can return the nearest lower threshold, not necessarily an exact match. Use FALSE for category labels and verify ascending order for threshold tables.
#REF!: The row index may be greater than the number of rows in the selected table, or a referenced range may have been removed.
#VALUE!: A row index below 1 is invalid. The row index should be a positive number that identifies a row inside the table array.
Wrong heading matched: HLOOKUP searches the first row of the selected range only. Make sure the range starts with the intended lookup headings, not a title or an unrelated header.

Text matching is generally not case-sensitive: P-103 and p-103 are treated as the same lookup text. However, other differences can still matter. A code stored as text may not behave like a numeric value, and leading or trailing spaces can prevent an apparent match. Cleaning or standardizing source data is preferable to building increasingly complicated lookup formulas around inconsistent entries.

HLOOKUP Compared with Other Lookup Options

HLOOKUP is a reasonable choice when a small table is genuinely horizontal or when an existing workbook already depends on it. Its limitation is that the return row is selected by a number. Adding or rearranging rows can make formulas harder to audit, and it cannot return a value to the left of the lookup row because the lookup row is always at the top of the selected range.

In Microsoft 365, XLOOKUP can search one horizontal row and return a value from another horizontal row, with a separate option for what to show when no match is found. With product codes in A1:D1, prices in A2:D2, and the requested code in G1, this formula returns the matching price or Not found:

=XLOOKUP(G1,A1:D1,A2:D2,"Not found")

XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not available in older Excel releases. HLOOKUP remains useful when compatibility with older workbooks matters. If the table can be reorganized, placing each record in a row with field names in a header row is often easier to filter, sort, and extend than maintaining a wide horizontal list.

Frequently Asked Questions

What does the H in HLOOKUP stand for?
It stands for horizontal. HLOOKUP searches across the first row of a selected range, then returns a value from a specified row in the matching column.

Should I use TRUE or FALSE in HLOOKUP?
Use FALSE for an exact match, which is the right choice for most beginners working with codes or labels. Use TRUE for sorted numeric breakpoints when you want the result associated with the greatest threshold not exceeding the lookup value.

Why does HLOOKUP return #N/A when the value looks identical?
The value may differ in a way that is hard to see, such as an extra space, or one value may be stored as text while the other is numeric. Check the actual first-row headings, the selected range, and the match setting. HLOOKUP does not distinguish uppercase from lowercase.

Can HLOOKUP search down a column?
No. HLOOKUP searches horizontally across the first row of its table array. For a lookup value in the first column, use a function designed for vertical lookups, such as VLOOKUP, or use XLOOKUP where available.

Does HLOOKUP work in Excel for Mac and Excel for the web?
HLOOKUP is available in current Excel for Windows, Excel for Mac, and Excel for the web. The formula logic is the same; ribbon layout and some interface details can differ, but the examples here do not depend on a Windows-only command.

Choosing a Reliable Setup

Before copying a formula down or sharing a workbook, verify three things: the lookup values are in the first row of the selected range; the return-row number points to the intended information; and the match mode fits the data. Exact-match lookups are usually the best starting point for identifiers. Approximate matches should be reserved for ordered ranges with clear boundaries.

Keep headings consistent, avoid merged cells inside lookup tables, and test both a value that should match and one that should not. A quick check with a known code can reveal a misaligned range before the formula is used for a larger list. When the table is likely to grow or change shape, consider a more flexible lookup design rather than relying on a fixed row index.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.