Knowledge base / Excel
HLOOKUP in Excel: exact matches and ordered thresholds
I retrieve values from a horizontal table and explain why product searches and ordered thresholds need different match modes.

Your products run across the first row, with prices and stock below. I check the data direction first: this is a horizontal lookup, so HLOOKUP fits an older workbook. It finds a column using the top row of the supplied range, then retrieves a value from a chosen row in that same column. The Polish function name is WYSZUKAJ.POZIOMO.
For a specific product, I explicitly enter FALSE as the fourth argument. Omitting it selects approximate matching, which needs an ascending first row. Microsoft documents these rules in the HLOOKUP reference. The small datasets below are synthetic.
Syntax and the row index
=HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])
The third argument counts rows inside the supplied range, starting at 1. It is not the worksheet row number. If a range starts at A10, its second row is worksheet row 11. These English formulas use commas; regional settings may require a different argument separator. HLOOKUP is available in Microsoft 365 and Excel versions including 2016, 2019, 2021 and 2024.
Before writing the formula, point to the range’s first row, the matching product column and the result row. Doing that on a small table exposes a misunderstood index before a large range makes the problem harder to see. A formula can use valid references yet retrieve stock when you intended price. The meaning of the selected cells matters as much as their addresses.
Retrieve price and stock
Enter this table in A1:D3. The Polish labels are retained so both language versions use identical input: Pole means Field, Cena means Price and Stan means Stock. Products occupy columns B:D. Enter prices as numbers recognised by your locale; the decimal comma below describes the same numeric values as 3.50, 4.20 and 5.00.
| Pole | Jabłka | Banany | Pomarańcze |
|---|---|---|---|
| Cena | 3,50 | 4,20 | 5,00 |
| Stan | 12 | 0 | 8 |
=HLOOKUP("Banany",$A$1:$D$3,2,FALSE)
The match is column C. The second row of the range contains prices, so the result is 4.20. Change the row index to 3 and the result becomes 0, the stock recorded in C3. Dollar signs keep the table boundaries fixed when copying. You can replace the literal product name with F1 to let someone choose which product to retrieve.
With “Pomarańcze” in F1, =HLOOKUP(F1,$A$1:$D$3,2,FALSE) selects D2, giving 5.00. The selected column changes, while the result row stays fixed. This separates two roles clearly: the lookup value chooses the product, and the row index chooses the information to return. Use that distinction when investigating a plausible number that answers the wrong question.
Missing products and invalid indexes
“Gruszki” does not occur in the first row, so exact matching returns #N/A. That differs from the genuine zero stock for Banany. Replacing both outcomes with zero would hide the distinction between a known product with no stock and a product absent from the lookup list. Microsoft describes missing-match diagnosis separately from valid returned values.
In this three-row range, an index of 4 exceeds its height and returns #REF!. An index below 1 returns #VALUE!. Also inspect valid but unintended indexes: 3 returns stock rather than price. No error message appears because the reference itself is valid. A useful check is to identify the expected source cell manually and compare the formula’s role with it.
Clean accidental spaces and agree a consistent key before lookup. If a product heading occurs more than once, refine the key with information such as variant or warehouse. These examples assume unique headings. I do not use a single retrieved result to resolve ambiguous duplicate products: deciding which record is appropriate requires a business rule first.
Approximate matching for thresholds
In a separate area, B6:E7, enter ascending numeric thresholds and genuine percentage values beneath them. The second row contains 0%, 5%, 10% and 15%, rather than whole numbers merely labelled as percentages.
| B | C | D | E |
|---|---|---|---|
| 0 | 10 | 50 | 100 |
| 0% | 5% | 10% | 15% |
=HLOOKUP(70,$B$6:$E$7,2,TRUE)
The largest threshold not exceeding 70 is 50, so the result is 10%. An input of 50 also gives 10%; 49 gives 5%; 0 gives 0%. A negative input lies below the smallest threshold and produces no match. In a real order form, reject such an invalid quantity before assigning its discount rather than silently treating it as an ordinary order.
Sort the first row ascending from left to right while keeping each discount attached to its threshold. Approximate matching does not mean the numerically nearest value on either side. For 90, it still chooses 50 even though 100 is closer. The threshold table makes that rule visible and gives the person maintaining the workbook a simple way to inspect boundary behaviour.

XLOOKUP in a newer workbook
For the original product table, the appropriate horizontal alternative is:
=XLOOKUP("Banany",$B$1:$D$1,$B$2:$D$2)
It searches the product row B1:D1 and returns from the price row B2:D2, giving 4.20. Ranges A1:A2 and B1:B2 would be vertical and would not represent this layout. Check both range direction and meaning. Equal-sized ranges can still be the wrong pair if they describe unrelated parts of the worksheet.
XLOOKUP uses exact matching by default. Microsoft’s XLOOKUP documentation lists Microsoft 365, Excel 2021 and Excel 2024, and explicitly excludes Excel 2016 and 2019. I would consider it for new work after checking the recipient’s version. The dedicated XLOOKUP article develops the options further.
Exercise with two kinds of result
Enter “Jabłka” in F1 and retrieve its price and stock separately: expect 3.50 and 12. Change the product to “Gruszki” and explain how a missing match differs from zero stock. In the threshold table, derive the results for 9, 10 and 100: 0%, 5% and 15%. Finally, write one sentence explaining why a named product needs exact matching while discounts use ordered thresholds.
Key takeaways
Does HLOOKUP use exact matching by default?
No. Omitting the fourth argument selects TRUE and approximate matching. Enter FALSE for an exact product search.
What does the row index count?
It counts rows inside the supplied table range, starting at 1. It is not the worksheet row number.
What must be sorted for approximate matching?
Sort the first row ascending from left to right, preserving the corresponding values beneath each threshold.
AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.
