Succeeded by XLOOKUP

Use XLOOKUP, unless the file has to work in older Excel.

HLOOKUP has served well for tables that run left to right and it still works. But since Excel 2021 there is a successor that does the same job with less counting and fewer silent mistakes: XLOOKUP.

Which one should I use?

Everyone who opens the file has Excel 2021 or Microsoft 365.
Use XLOOKUP.
The file must also work in Excel 2019 or older, for example a client's file or a shared template.
Use HLOOKUP.
You are editing an old workbook full of HLOOKUPs.
Leave formulas that work alone, and switch when you rework that part.

Go to XLOOKUP

Why XLOOKUP is better

  • It looks upwards too. The row with the keys no longer has to be the top row of the table.
  • No row number to count. You point at the row you want back, so inserting a row does not break the formula.
  • Exact match is the default. Forget the FALSE at the end of HLOOKUP and it returns a wrong value without a warning; XLOOKUP does not.
  • A built-in message for missing values, without wrapping the formula in IFERROR.
  • It can search from right to left, to find the latest period with an entry.

HLOOKUP (every version)

=HLOOKUP(H2, A1:F3, 3, FALSE)
=HLOOKUP(H2; A1:F3; 3; FALSE)

XLOOKUP (Excel 2021 and Microsoft 365)

=XLOOKUP(H2, A1:F1, A3:F3)
=XLOOKUP(H2; A1:F1; A3:F3)

Need HLOOKUP for an older file?

The full HLOOKUP page has the syntax, the arguments, the common mistakes and four worked examples, for files that have to work in Excel 2019 or older.

Open the full HLOOKUP page