# How to do an approximate match across a row with HLOOKUP

Use TRUE as HLOOKUP's last argument with the thresholds sorted left to right: `=HLOOKUP(I1,B1:F2,2,TRUE)`.

**Excel:** `=HLOOKUP(I1,B1:F2,2,TRUE)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H | I |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Sales from | 0 | 50000 | 100000 | 150000 | 250000 |  | Sales | 128000 |
| Bonus rate | 0.005 | 0.01 | 0.02 | 0.03 | 0.05 |  | Bonus rate |  |

Formula in I2: `=HLOOKUP(I1,B1:F2,2,TRUE)`

Result: 2.0%

## How it works

With TRUE, HLOOKUP finds the largest threshold in the first row that is not more than the value: $128,000 lands on the $100,000 column, and row 2 of the table gives its rate, 2%. It is the horizontal version of VLOOKUP's approximate match, for tables laid out across.

## Other ways

- `=XLOOKUP(I1,B1:F1,B2:F2,,-1)`: Match mode -1 does the same and doesn't need the thresholds sorted.
- `=INDEX(B2:F2,MATCH(I1,B1:F1,1))`: MATCH with 1 on a row works the same way, in every version.

## Common mistakes

- The thresholds must increase from left to right, or the result is wrong without warning.
- The first threshold should be the lowest possible value (0 here), or smaller sales return #N/A.

## Questions

**How do I calculate the bonus amount?**

Multiply by the sales: `=I1*HLOOKUP(I1,B1:F2,2,TRUE)`.

**What if the thresholds are listed down a column?**

Use VLOOKUP with TRUE, or XLOOKUP with -1, on the vertical table.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/hlookup-approximate-match