Spreadsheet Practice

How to do an approximate match across a row with HLOOKUP

Formula (Excel and Google Sheets)

=HLOOKUP(I1,B1:F2,2,TRUE)

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

Result on the example
2.0% in I2
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in I2. Change any input and the result updates, or edit the formula to experiment.

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.

=HLOOKUP(I1,B1:F2,2,TRUE)

Result0.02

  1. I1$128,000

    The value in I1.

  2. B1:F22 rows × 5 cols: $0, $50,000, $100,000, $150,000, …

    A range: 2 rows × 5 columns.

  3. HLOOKUP(I1,B1:F2,2,TRUE)0.02

    HLOOKUP: Horizontal version of VLOOKUP.

    • lookup_valueI1$128,000
    • table_arrayB1:F22 rows × 5 cols: $0, $50,000, $100,000, $150,000, …
    • row_index_num2
    • range_lookupTRUE

In Google Sheets

The same formula works in Google Sheets: =HLOOKUP(I1,B1:F2,2,TRUE). We ran it in Google Sheets mode on the same data and it returns 2.0%.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=XLOOKUP(I1,B1:F1,B2:F2,,-1)2.0%Match mode -1 does the same and doesn't need the thresholds sorted.
=INDEX(B2:F2,MATCH(I1,B1:F1,1))2.0%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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Do an approximate match across a row with HLOOKUP

The bonus thresholds run across row 1. In I2, return the bonus rate for the sales in I1.

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.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.