XLOOKUP · Excel · CodeMIRAI

XLOOKUP (Microsoft 365, Excel 2021 and later, and Google Sheets) does everything VLOOKUP does, more simply and with fewer traps.

Excel formula

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

This lesson uses an employee list in A1:D6:

	A	B	C	D
1	ID	Name	Department	Salary
2	E101	Grace	Engineering	210000
3	E102	Alan	Engineering	165000
4	E103	Maya	Sales	150000
5	E104	Omar	Sales	92000
6	E105	Hana	Support	71000

The basics

Excel formula

=XLOOKUP("E104", A2:A6, B2:B6)        → Omar
=XLOOKUP("E104", A2:A6, D2:D6)        → 92000

XLOOKUP searches one range and returns the item at the same position from another, so the result column can be anywhere.

Give it what to find, where to find it, and which column to return. Unlike VLOOKUP:

it does an exact match by default, so there's no FALSE to forget;
the return column is a range, not a column number, so inserting a column doesn't break it.

Looking left

Excel formula

=XLOOKUP("Maya", B2:B6, A2:A6)        → E103

The return range can be to the left of the lookup range, which VLOOKUP simply can't do.

When nothing matches

Excel formula

=XLOOKUP("E999", A2:A6, B2:B6)                        → #N/A
=XLOOKUP("E999", A2:A6, B2:B6, "No such employee")    → No such employee

The fourth argument replaces the #N/A error with a message of your choice, with no need to wrap the formula in IFERROR.

Returning several columns

Excel formula

=XLOOKUP("E102", A2:A6, B2:D6)        → Alan | Engineering | 165000

With a multi-column return range, the results spill into the cells to the right: one formula fills the name, department and salary.

Approximate matches: bands and grades

A grade table with each band's lower limit in F2:F6:

	F	G
1	From	Grade
2	0	F
3	50	D
4	60	C
5	75	B
6	90	A

Excel formula

=XLOOKUP(82, F2:F6, G2:G6, , -1)      → B     the band starting at 75
=XLOOKUP(90, F2:F6, G2:G6, , -1)      → A     an exact match counts too

The fifth argument, match_mode, chooses the kind of match:

`match_mode`	Finds
`0` (the default)	an exact match only
`-1`	an exact match, or else the next smaller value
`1`	an exact match, or else the next larger value
`2`	a wildcard match, using `*` and `?`

With -1, 82 has no exact match, so XLOOKUP takes the next smaller limit, 75. Unlike VLOOKUP's approximate mode, the table doesn't even need to be sorted.

Wildcards

Excel formula

=XLOOKUP("Ma*", B2:B6, D2:D6, , 2)    → 150000    the first name starting with Ma

Searching from the bottom

Excel formula

=XLOOKUP("Sales", C2:C6, B2:B6)             → Maya    the first Sales row
=XLOOKUP("Sales", C2:C6, B2:B6, , 0, -1)    → Omar    the last Sales row

The sixth argument, search_mode, set to -1, searches from the last row upwards: useful for finding the most recent entry in a log or a list of transactions.