Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 93 ยท Lookup and Reference

HLOOKUP and Approximate-Match Lookups

Learn HLOOKUP and approximate-match lookups in Excel: sorted ranges, grade bands, tax brackets, and how the binary search behind them can go wrong.

Not every lookup is "find this exact ID". Sometimes the question is which band does this number fall into? A score of 85 is a B because it is at least 80 but below 90. An income of 55,000 falls in the bracket that starts at 40,000. A shipping weight of 2.3 kg belongs to the "up to 5 kg" price. No row in the table equals the number, yet there is a clear right answer.

This is what approximate-match lookups are for. In this lesson you will use HLOOKUP, the horizontal sibling of VLOOKUP, and use approximate matching for grade bands and rate tables. You will also see how Excel finds the answer with a binary search, and why that makes sorted data a hard requirement.

Excel cannot run on this site. The formulas are genuine Excel syntax; the live examples run them on a small JavaScript stand-in engine (same engine as the previous lessons, plus a Python version). They behave like Excel for these functions but are not its real code.

Exact vs approximate

Exact The key must be in the list. Any order.
Approximate Finds the largest key that is not above yours. Sorted only.

Approximate answers "which band?" questions.

1 / 2

HLOOKUP: the horizontal lookup

HLOOKUP ("horizontal lookup") works exactly like VLOOKUP, turned on its side: it searches the first row of a table and returns a value from a row you name by number.

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

Use it when your keys run across the top, for example months in B1:M1, or a grade scale laid out horizontally:

TEXT
     A    B    C    D    E
1    0   60   70   80   90      <- lower limits (sorted ascending)
2    F    D    C    B    A      <- the grade for each limit

=HLOOKUP(85, A1:E2, 2) searches row 1 for the largest limit that is not above 85. That is 80 in column D, so the formula returns the value from row 2 of that column: B. All the rules from VLOOKUP and Its Limits carry over: the row number is hard-coded and counted inside the block, and the last argument defaults to TRUE.

In practice, HLOOKUP is used less often than VLOOKUP, because data is usually arranged in columns (see Excel Tables). Learn it for the day you inherit a horizontal layout. XLOOKUP (coming up) handles both directions with one function.

How approximate match works

With the last argument TRUE (or omitted), the rules are:

  1. The first column (for VLOOKUP) or first row (for HLOOKUP) must be sorted ascending.
  2. Excel returns the row of the largest value that is less than or equal to the lookup value.
  3. If the lookup value is smaller than every key, the result is #N/A.
  4. If the lookup value is larger than every key, you get the last row. There is no "too big".

That is why band tables hold lower limits: 0, 60, 70, 80, 90. Every score from 80 up to (but not including) 90 finds the 80 row.

The example below uses both orientations. HLOOKUP turns scores into grades. VLOOKUP turns incomes into a bracket's rate. Look at the boundary cases: 59 versus 60, 9999 versus 10000, and the -3 that sits below the table's first limit.

JS Grade bands and rate brackets

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Note how the boundaries behave:

  • 59 is an F, but 60 is a D: the limit itself belongs to the band it starts.
  • -3 gives #N/A because the table's first limit is 0. Starting your table at 0 (or at a value lower than any possible input) avoids this.
  • 100 gives A: values above the last limit stay in the last band.
  • With FALSE, 85 has no exact match, so exact mode returns #N/A. Use the right mode for the question.

A bracket rate is not a tax amount

The second table returns only the rate of the bracket. For a progressive tax, each slice of income is taxed at its own rate, so the total is a sum over slices and a plain lookup is not enough. Pairing a lookup with a "cumulative tax at the bottom of the bracket" column is a common fix, and the Python example below shows the slice-by-slice idea.

For a long list, checking every row would be slow. Approximate match uses binary search: look at the middle row, discard the half that cannot contain the answer, repeat. A million rows need only about twenty probes. It is the reason approximate VLOOKUP can be faster than exact VLOOKUP on very large sorted tables.

The cost is that the search trusts the sorting. The stand-in below prints each probe. It runs on sorted data, on a million rows, and then on an unsorted list.

JS Binary search: fast on sorted data, wrong on unsorted data

Press Run (or Ctrl + Enter) to see the console output here.

No output.

On the sorted list, four probes find 70 as the largest limit not above 77. With a million rows it still takes just 20. On the unsorted list the search followed the middle values, concluded that everything to the right was too big, and answered 0, although the true answer, 30, was sitting at the end. No error appeared. Excel's own implementation is a binary search too, so on unsorted data it can also return an arbitrary row; the exact wrong answer may differ from this stand-in.

If you use approximate match on data that someone can re-sort or edit, add a check, for example =IF(SUMPRODUCT(--(A6:A8<A5:A7))=0, "sorted", "NOT SORTED") for keys in A5:A8 (the shifted ranges compare each key with the one above it). Or avoid the problem and use XLOOKUP with its clear match modes.

Tips for band tables

  • Keep the table on its own sheet or in a named range, so graders and rates can be changed in one place instead of being hidden inside nested IFs (compare IF and Nested IF).
  • Store limits as numbers, never as text like "60+".
  • Use the lower bound of each band, as above. If your source describes upper limits ("up to 5 kg"), convert them or use XLOOKUP's "next larger item" mode.
  • Order matters for text too: approximate text lookups use alphabetical order, ignoring case.
  • Test the edges: the exact limit, one below, one above, below the first, above the last.

The same lookup in Python

Python's bisect module is a binary search over a sorted list, which is what approximate match does. bisect_right(limits, score) - 1 is "the last limit less than or equal to the score". pandas' cut does the same for a whole column. The last block computes a progressive tax slice by slice.

Python bisect, cut and a progressive tax

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The slice-by-slice total for an income of 55,000 is 10,000 ร— 0.10 + 30,000 ร— 0.12 + 15,000 ร— 0.22 = 7,900, which matches the printed result. A lookup of the bracket rate alone (0.22) would have given a much bigger, wrong tax.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A grade table has the lower limits 0, 60, 70, 80, 90 in ascending order. What does an approximate-match lookup of 80 return?

    Approximate match returns the largest key that is less than or equal to the lookup value. 80 equals a key, so the 80 row is used; the limit itself belongs to the band it starts.
  2. 2. What does approximate match return when the lookup value is smaller than the first key?

    There is no key that is less than or equal to the value, so the result is #N/A. Starting a band table at 0 prevents this.
  3. 3. Why must the key column be sorted ascending for approximate match?

    Binary search discards half of the list at each step based on the order. On unsorted data it can discard the right half and return a wrong row, without any error message.
  4. 4. Which function searches the first ROW of a table and returns a value from a row you choose by number?

    HLOOKUP is the horizontal version of VLOOKUP: it searches the top row and returns from the row at the given index.

Summary

  • HLOOKUP(key, table, row_number, [range_lookup]) is VLOOKUP turned sideways: it searches the first row.
  • Approximate match (TRUE or omitted) returns the largest key less than or equal to the lookup value; the keys must be sorted ascending.
  • Band tables hold lower limits and should start at a value below any input, otherwise you get #N/A.
  • Excel uses a binary search for approximate mode: very fast, but silently wrong when the data is not sorted.
  • A bracket rate is not the same as a progressive tax amount; compute it slice by slice.

Next, the pair that removes most of VLOOKUP's limits: INDEX and MATCH.