HLOOKUP Examples for Practice: Formulas and Fixes in Excel

For an exact-match HLOOKUP practice example, put product codes in A1:D1, prices in A2:D2, and stock counts in A3:D3; enter =HLOOKUP("P103",$A$1:$D$3,2,FALSE) to return the price, 18.75. HLOOKUP searches across the top row of a range and returns a value from a specified row beneath the match.

What HLOOKUP does

HLOOKUP means horizontal lookup. It is useful when the values you want to search are arranged left to right across the first row of a table, while the information to return is in rows below. For example, a quarterly report might have quarter names across its top and revenue, costs, and profit on separate rows beneath them. You can search for a quarter and return the figure from the row you choose.

The function has four arguments: the value to find, the 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. In a range beginning at A1, row index 1 means worksheet row 1; row index 2 means worksheet row 2. This detail is a frequent source of wrong results.

HLOOKUP searches only the first row of its selected range. It does not search every row, and it does not automatically return a value from the row whose label matches a heading on the left. Its layout is therefore the reverse of VLOOKUP: HLOOKUP is suited to tables arranged horizontally, while VLOOKUP searches down the first column.

Set up a small practice table

Enter the following sample data in cells A1:D3. The first row contains product codes; the second and third rows contain different details for those products.

Row	A	B	C	D
1	P101	P102	P103	P104
2	12.50	15.00	18.75	9.50
3	8	0	14	6

Here, row 2 represents prices and row 3 represents stock counts. The dollar signs in the formula below keep the table range fixed if you later copy the formula to another cell. With the product table in A1:D3, enter this formula in any empty cell to find the price for P103:

=HLOOKUP("P103",$A$1:$D$3,2,FALSE)

The result is 18.75. Excel finds P103 in the first row of the selected range, then returns the value from the second row of that range in the same column. The final argument, FALSE, asks for an exact match. This is generally the safer setting for product codes, names, IDs, and other labels.

Return a different row from the same match

Once the lookup row is correct, the row index determines which related information comes back. For stock instead of price, keep the same table but change the index from 2 to 3. With the product table in A1:D3, enter:

=HLOOKUP("P103",$A$1:$D$3,3,FALSE)

The result is 14, the stock count in row 3 under P103. The index 3 means the third row inside A1:D3; it does not mean worksheet row 3 in every possible table. If the selected range began at A5, its third row would be worksheet row 7. When adapting a practice formula, check the top-left cell of the table range and count down from there.

You can also look up a code stored in a cell rather than typing it into the formula. Suppose F1 contains P103 and the product table remains in A1:D3. Enter this formula in F2:

=HLOOKUP(F1,$A$1:$D$3,2,FALSE)

It returns 18.75. Changing F1 to P101 changes the result to 12.50, so the same lookup can serve as a small interactive report. If you want to copy the formula down for a list of requested codes in F1:F10, the fixed table references keep pointing to A1:D3 while the lookup reference adjusts by row.

Practice approximate matches with thresholds

HLOOKUP can also find the nearest lower threshold rather than requiring an exact match. This is useful for banded rules such as a rate table, grading scale, or shipping bracket. For practice, put the thresholds 0, 100, 500, and 1000 across A1:D1, in ascending order. Put 0%, 5%, 10%, and 15% in the corresponding cells A2:D2.

With those threshold values in A1:D2, this formula looks up a value of 640:

=HLOOKUP(640,$A$1:$D$2,2,TRUE)

The result is 10%. In approximate-match mode, Excel selects the largest value in the first row that is less than or equal to 640: that is 500. It then returns the value beneath 500. The thresholds must be sorted in ascending order for this approximate-match behavior to be reliable. If the first row is unsorted, the result can be unexpected.

Approximate matching is not the same as “pick whichever number is closest.” For example, 640 is nearer to 500 than to 1000, but the rule is specifically to use the greatest threshold not exceeding the lookup value. If a value is below the smallest threshold, there is no eligible lower threshold and HLOOKUP returns #N/A. Choose approximate matching only when the table is deliberately designed around ordered breakpoints.

Turn these examples into practice exercises

Try changing the lookup value and return-row index before reading the results. These exercises build the two core skills: selecting the correct column by matching the top row, and returning the intended row of information.

Using the product table in A1:D3, look up P102 and return row 2. The expected result is 15.00.
Using the same table, look up P104 and return row 3. The expected result is 6.
Using the threshold table in A1:D2, look up 250 with approximate matching. The expected result is 5%, because 100 is the largest threshold not exceeding 250.
Change the exact-match product lookup to P999. Before adding error handling, the expected result is #N/A because P999 does not appear in the first row.

For a reusable worksheet, put the requested product codes in F1:F4 and place formulas beside them. With the lookup code in F1 and the product table in A1:D3, enter this in G1 and copy it down:

=HLOOKUP(F1,$A$1:$D$3,2,FALSE)

For the sample codes P103, P101, P104, and P102, the returned prices are 18.75, 12.50, 9.50, and 15.00. This setup makes the input list easy to change without editing the formula each time. If you copy the formula horizontally instead, consider whether the lookup reference should keep its row fixed; a reference such as F$1 locks the row while allowing the column to change.

Handle missing values and common errors

The most common result that needs investigation is #N/A. With an exact match, it usually means the lookup value is not an exact match for an entry in the first row. Check for extra spaces, different punctuation, mismatched number-versus-text types, or a code that is simply absent. A cell containing the number 123 may not match a cell containing the text string “123” in the way a user expects.

If you want a clearer message for a missing product, IFNA can catch the #N/A result. With the product table in A1:D3, enter:

=IFNA(HLOOKUP("P999",$A$1:$D$3,2,FALSE),"Code not found")

The result is Code not found. IFNA leaves other errors visible, which can help distinguish a missing lookup value from a different formula problem. Avoid wrapping every lookup in IFERROR without considering the effect: IFERROR can conceal errors such as an invalid row index that you may want to fix rather than disguise.

A #REF! error can occur if the row index is greater than the number of rows in the selected range. For instance, a range with only two rows cannot return row 3. A #VALUE! error may occur if the row index is less than 1. Also confirm that the selected range starts with the row containing the lookup keys. Including a title row above the keys changes which row HLOOKUP searches.

Use FALSE for exact text or ID searches; TRUE is for ordered approximate thresholds.
Count the return row from the top of the selected range, not from the worksheet.
Include the full width of the lookup table so the desired key is inside the selected range.
Check for spaces and inconsistent data types when a value that appears present returns #N/A.

When HLOOKUP is the right tool

HLOOKUP is a reasonable choice when a table is already arranged horizontally and you want a direct formula that teammates can recognize. It also works well in older workbooks where compatibility with earlier Excel versions matters. It is not necessary to rearrange a small, stable table solely to avoid HLOOKUP if the horizontal layout is useful to the people reading the sheet.

Its main limitation is that the lookup keys must occupy the top row of the selected range, and the return row is chosen by a numeric position. Inserting or removing rows inside the table can make a hard-coded row index point to different information. A formula can remain syntactically valid while returning the wrong kind of data if the table structure changes, so label the rows clearly and review formulas after structural edits.

If you design a new table, consider whether a vertical layout would be easier to maintain. Records commonly work well with one item per row and field names across columns. That arrangement supports filtering and sorting naturally and allows other lookup approaches to search a key column. The best direction is the one that keeps the source data clear and minimizes fragile formulas.

Compare HLOOKUP with modern alternatives

XLOOKUP can search horizontally when its lookup row and return row are supplied separately. It does not use a numeric row index, and it can provide a custom not-found result directly. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but it is not available in some older Excel releases. With product codes in A1:D1 and prices in A2:D2, this formula returns 18.75 for P103:

=XLOOKUP("P103",$A$1:$D$1,$A$2:$D$2,"Not found")

INDEX with MATCH is another option. MATCH finds the position of a key across the top row, and INDEX returns the corresponding position from the price row. With codes in A1:D1 and prices in A2:D2, this formula also returns 18.75:

=INDEX($A$2:$D$2,1,MATCH("P103",$A$1:$D$1,0))

These alternatives can be more flexible, but HLOOKUP remains useful for learning how table lookups work and for maintaining familiar workbooks. In Microsoft 365 for Windows, the formulas above use standard Excel syntax. The same functions are available in Excel for the web; the interface may differ, but the formula logic is the same. Excel for Mac also uses the same formulas, although ribbon locations and some keyboard shortcuts can differ from Windows.

Frequently Asked Questions

What does the row index number mean in HLOOKUP?
The row index is the position of the return row within the selected table range. The first row of that range is 1, the next is 2, and so on. It is not an absolute worksheet row number.

Should I use TRUE or FALSE in HLOOKUP?
Use FALSE when you need an exact match, which is the usual choice for names, product codes, and IDs. Use TRUE for an approximate threshold lookup only when the first row is sorted in ascending order and the lower-threshold rule is appropriate.

Why does HLOOKUP return #N/A when the value is visible?
The value may differ slightly from the lookup key because of extra spaces, punctuation, or a number stored as text. Also make sure the key is in the first row of the selected range and that the match setting is appropriate.

Can HLOOKUP search from bottom to top?
No. HLOOKUP searches across the first row of its table range from left to right. It does not provide a reverse-search option. If reverse searching or more flexible matching is important, consider XLOOKUP in a supported Excel version.

Is HLOOKUP available in Excel for the web and Mac?
Yes. HLOOKUP is available in Excel for Microsoft 365 on Windows, Excel for the web, and Excel for Mac. The formula syntax is the same, although some interface paths and shortcuts can vary by platform.

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.