Excel Function

XLOOKUP

Modern lookup function that can return values to the left or right of the lookup column, unlike VLOOKUP.

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

Arguments

lookup_value

Required

Enter the value you want to look up.

lookup_array

Required

Specify the row/column range where the lookup key is searched.

return_array

Required

Specify the range to return when a match is found.

if_not_found

Optional

Specify the value returned when no match is found.

Practice Preview

Use XLOOKUP to find the value in cell B2 (Product ID in column B) within Product ID in column B, return the value from Product Name in column A, and return "Not registered" when it is not found. You can set ranges by dragging from the start cell to the end cell in the same argument field.

Example Table

#
AProduct Name
BProduct ID
CPrice
DStock
2AppleP00112080
3BananaP00298120
4NotebookP00345035
5PenP00430060
6GrapesP00515045
7RulerP00625020
8PeachP0078655

How to Use (Formula)

=XLOOKUP(B2, B2:B8, A2:A8, "Not registered")

Result

Apple

Practice This Function

Funktionsuebung startenBack to Function List

XLOOKUP FAQ

How do I use XLOOKUP in Excel?

Enter the lookup value, lookup range, and return range in order. Unlike VLOOKUP, XLOOKUP uses an explicit return range instead of a column number, so it can return values on the left side of the lookup column. For example, to return the product name to the left of a product ID, use XLOOKUP(B2, B2:B8, A2:A8, "Not registered").

Where can I practice XLOOKUP?

Use the "Start Function Practice" button to jump straight into XLOOKUP drills.

What arguments does XLOOKUP use?

The main arguments are Lookup value, Lookup array, Return array, If not found. Start with the required ones, then add optional arguments if needed.