XLOOKUP is a Formula 1

XLOOKUP has been released, only for Microsoft 365. This has changed the way to find data in document.

Merger of VLOOKUP and INDEX

The functions VLOOKUP and INDEX return a value from a reference table. But both functions have there pros and cons.

VLOOKUP INDEX
Pros Easy to build Research to the left
Cons Exact match isn't the default research Need the position in the range

XLOOKUP gather the pros of these 2 functions and remove the cons 😍😀👍.

  • X comes from the indeX function
  • LOOKUP comes from vLOOKUP function

How to build XLOOKUP?

XLOOKUP is easier to build that VLOOKUP or INDEX.

  • First, the value to search
  • Second, the column where to search the value
  • Third, the column to return

Examples of research with XLOOKUP

For instance here, we want to return the city according to the ID, the email. I have put my data in a Table to visualise the column name in the formula.

=XLOOKUP(C2,TbClient[EmailAddress],TbClient[City])

Returns a value with XLOOKUP

But now, If I want to return a value from a column on the left of my pivot column, I just have to change the 3rd argument and select the new column

The location of the columns has no impact on the research (better than VLOOKUP 😉)

=XLOOKUP(C2,TbClient[EmailAddress],TbClient[GivenName])

XLOOKUP returns a value to the left

Return more than 1 column

XLOOKUP is available only with Microsoft 365 and Excel Online and they manage dynamic arrays.

A dynamic arrays means that a formula can return a result in more than 1 cell. So, if you fill a range of cells for the 3rd argument, then the function will return the result in more than 1 column.

Return the result in more than 1 column

Manage #N/A (Unknown value)

In Excel, when a desired value doesn't exist in the reference table, the functions VLOOKUP or INDEX return #N/A. In some situation, like compare 2 columns, #N/A is the result that we want to find.

The test returns the result expected

With XLOOKUP, it's the same. Here, the email is unknown so the result is #N/A.

XLOOKUP returns NA when the value is unknown

But, if you fill the 4th argument of the function, you can specify a result in case of error in the search.

The 4th argument of XLOOKUP manage the error

Related posts


Permanent link to this article: https://www.excel-exercise.com/xlookup-is-a-formula-1/

Leave a Reply

Your email address will not be published.