Excel — what is XLOOKUP and when to use it.

XLOOKUP allows vertical and horizonal lookup. This is the successor function that can replace VLOOKUP, HLOOKUP, INDEX Match and more.

XLOOKUP is only available with Microsoft 365 and Excel 2021

Let’s see how to use it.

The mechanics of XLOOKUP.

=XLOOKUP(Search for this value, In this range, Return a match from this range)

XLOOKUP Columns

XLOOKUP Return value from Column range

XLOOKUP Rows

XLOOKUP Return value from Row range

BONUS — XLOOKUP Match options

XLOOKUP defaults to exact match. What if you want the next biggest if there is no match? Or next smallest?

The fifth argument controls this.

0 or omitted — exact match; if not found — #N/A error.

XLOOKUP 5th Argument = 0

Try -1 if you want exact match; if not found — next smaller item.

XLOOKUP 5th Argument = -1

Or 1 for exact match; if not found — next larger item.

XLOOKUP 5th Argument = 1