LOOKUP functions are functions that return a certain value from a range or array. The VLOOKUP function has been in Excel since the software’s first incarnation in 1995, back in Excel 1 for Macintosh. It was incredibly useful and was for 3^rd most used function in Excel after SUM and AVERAGE. Problem was, VLOOKUP only searched vertically, and you’d need to use a different function (HLOOKUP) to look horizontally.

After 35 years, the time for a successor has come, and it’s called XLOOKUP, and it looks up entries both vertically and horizontally at once.

As you can see from Microsoft’s gif above, XLOOKUP needs only three arguments to work: lookup_value, lookup_array, and return_array. Essentially, the value you’re looking for, the array (columns or rows) you want to look for the value in, and what you want the function to return.

There are also advanced variations you can use with XLOOKUP’s optional 4^th and 5^th arguments. These are if_not_found, match_mode and ­search­_mode.

You can use if_not_found, which lets you specify what to return if a match isn’t found, and match_mode, where you can specify what type of match to perform by passing a number in the function’s arguments.

Zero is the default, and used for exact matches. Both 1 (and -1) allow matches against the nearest smaller (or larger) item when there’s no exact match. 2 is a wildcard for partial matches where the returns ? maches any character and * matches any run of characters.

In search_mode you can configure the type and direction of search. 1 will search first-to-last, -1 will search last-to-first; 2 is a binary search sorted in ascending order, and -2 is the same, but descending.

Can I Still Use old Lookup Functions?

Yes! Although XLOOKUP is faster than the older functions, if you have sheets where you’re using the old ones and it’s too much of a bother to change it, they’ll still work. But it makes no sense not to use XLOOKUP for newer ones from now on, since it’s simpler to use and has less limitations than its predecessors.

XMATCH

Another addition, and similar to XLOOKUP, XMATCH does pretty much the same, but returns the index of the matching item instead. As with XLOOKUP, XMATCH is much simpler to use and has less limitations than the older MATCH function.

Availability

If you’re an Office 365 subscriber in the monthly channel, then you can already use XLOOKUP! For those in the Semi-Annual channel, you’ll have to wait until later in the year, with a specific date yet to be announced.