HLOOKUP in Excel: product codes and thresholds

HLOOKUP is useful when identifiers occupy the top row of a table and the information you need sits below them. I begin by checking the layout and the kind of match required. A missing argument can cause more trouble than the search itself: Excel may return an approximate result where you expected a missing-product error.

I will use two synthetic examples: finding a product by code and selecting a rate from an ordered set of thresholds. Keeping those tasks separate matters more than memorizing the function’s argument names. Each requires a different agreement with the source table.

Syntax and search direction

Microsoft’s HLOOKUP documentation describes four arguments:

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

The function searches the first row of the selected range. After finding a column, it returns a value from that same column at the requested row index. Row 1 means the top row of the range, even when the table starts halfway down the worksheet. The index is unrelated to the worksheet row number shown at the edge of the window.

The examples use English function names, commas between arguments and decimal points. Polish Excel calls the function WYSZUKAJ.POZIOMO and commonly uses semicolons between arguments. Actual separators also depend on regional settings, so changing the function name alone may not make a copied formula suitable for a different installation.

Find an exact product code

Place the following layout in B2:E4. The first column below identifies worksheet rows for this explanation; it is not an extra column of lookup data.

Row 2 contains codes, row 3 prices in arbitrary units and row 4 stock quantities. Enter P-30 in G2, then put this formula in G3:

=HLOOKUP(G2,$B$2:$E$4,2,FALSE)

The expected result is 25. The code occurs in D2, and the second row of the lookup range is worksheet row 3, so the result comes from D3. Changing the third argument to 3 returns stock quantity 8 from D4. You can trace each reference directly rather than guessing what the index represents.

The dollar signs keep the table range fixed when the formula is copied. G2 remains a relative reference. For a list of lookup requests, copy the formula in the direction that matches that list and inspect the second result. One correct cell does not establish that the references will move as intended throughout the report.

Missing products, zero stock and invalid ranges

An exact search for P-99 returns #N/A. Searching for P-20 with row index 3 returns 0, because C4 explicitly contains zero stock. Those results describe different situations. I keep that distinction visible rather than replacing every error with a zero and leaving the reader to interpret both as the same business state.

For a friendly missing-code message, consider IFNA. Inspect the underlying lookup first. Catching every kind of error can conceal an invalid index or a table range that was shortened accidentally. An error message is useful evidence while you are still investigating why the result differs from expectations.

Our selected range has three rows, so index 4 exceeds it and returns #REF!. That is a different problem from a missing product. Check the requested code, the top row of the selected range, the range height and the result index. This sequence helps identify the source of a mistake without rebuilding the entire calculation.

Approximate matching selects a threshold

Omitting the fourth argument or setting it to TRUE enables approximate matching. The first row must be sorted ascending. If there is no equal value, the function selects the largest threshold below the lookup value. Describing that simply as the closest match can create the wrong expectation about how distances between numbers matter.

In a separate area B8:E9, enter these synthetic quantity thresholds and discount rates. The rates exist only for this exercise and do not describe an actual offer.

=HLOOKUP(18,$B$8:$E$9,2,TRUE)

The result is 2%, because 10 is the last threshold reached. Although 20 is numerically closer to 18, it has not been reached. A lookup of 20 returns 5%. A threshold schedule and a product dictionary therefore require different matching choices, even when both happen to fit into two worksheet rows.

Check boundaries and the source table

Check each threshold at the boundary and on both sides. Quantity 9 receives 0%, quantity 10 receives 2%, and quantity 11 still receives 2%. A value below the smallest threshold returns #N/A. If the schedule began at 10, a lookup of 9 would have no earlier bracket from which to obtain a rate.

Sort whole columns of the threshold table together. Sorting the thresholds while leaving their rates in place breaks the relationship between them. An ascending top row cannot rescue a schedule whose values no longer belong together. I also check the period for which the schedule is valid: a perfectly valid formula can still retrieve an obsolete business rule.

For exact matching, make product codes unambiguous. If two products share a key, agree a selection rule or repair the dictionary. One returned value is not proof that the identifier is unique. Separately inspect spaces, numbers stored as text and whether the identifiers in the request and source actually refer to the same thing.

HLOOKUP does not distinguish letter case. For exact text matching, it also supports ? and * wildcards, with a tilde used to escape them. If identifiers contain those characters literally, apply the documented rules. This article’s simple product codes avoid that complication so that the first exercise remains focused on direction and matching mode.

Consider XLOOKUP for an appropriate workbook

XLOOKUP lets you select the key and return arrays separately and uses exact matching by default. The equivalent price lookup is:

=XLOOKUP(G2,$B$2:$E$2,$B$3:$E$3,"missing code")

Check the recipient’s version: XLOOKUP is unavailable in Excel 2016 and 2019. HLOOKUP remains useful when maintaining an older workbook. For a broader comparison, see Excel lookup functions.

An exercise before using your own data

Return to the products. Retrieve the price of P-10, the stock of P-20 and the price of P-99. Expected results are 12, 0 and #N/A. Then use the threshold table to check quantities 19, 20 and 21. Explain in words why the rate changes at 20 and why a quantity of 19 still belongs to the earlier bracket.

Finally, write beside the formula whether it searches for an identifier or a threshold and which row supplies the result. That small piece of documentation helps the next person maintain the report. They can see the decision behind the last argument instead of inferring it from a formula that happens to return a plausible number.