Home » Function » Lookup functions » XLOOKUP is a Formula 1

# XLOOKUP is a Formula 1

Reading time: 2 minutes
Last Updated on 07/03/2021 by Frédéric LE GUEN

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])

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])

## 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.

## 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.

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

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

# XLOOKUP is a Formula 1

Reading time: 2 minutes
Last Updated on 07/03/2021 by Frédéric LE GUEN

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])

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])

## 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.

## 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.

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

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

{"mobile_concate":{"mobile-menu":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/mobile-menu.min.css"},"tf_base":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/css\/base.min.css","v":"7.1.1"},"themify_common":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/css\/themify.common.min.css","v":"7.1.1"},"builder-styles-css":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/themify-builder-style.min.css","v":"7.1.1"},"tb_tbp":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/plugins\/themify-builder-pro\/public\/css\/tbp-style.min.css","v":"3.0.8"},"tf_theme_site-logo":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/styles\/modules\/site-logo.min.css","v":"7.1.1"},"tf_theme_site-description":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/styles\/modules\/site-description.min.css","v":"7.1.1"},"tf_search_form":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/css\/modules\/search-form.min.css","v":"7.1.1"},"tb_image":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/image.min.css","v":"7.1.1"},"tb_image_top":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/image_styles\/top.min.css","v":"7.1.1"},"tb_divider":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/divider.min.css","v":"7.1.1"},"tb_post-content":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/text.min.css","v":"7.1.1"},"wp-block-library":{"s":"https:\/\/www.excel-exercise.com\/wp-includes\/css\/dist\/block-library\/style.min.css","v":"6.2"},"tb_comments":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/plugins\/themify-builder-pro\/public\/css\/modules\/comments.min.css","v":"3.0.8"},"tf_theme_social_links":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/css\/modules\/social-links.min.css","v":"7.1.1"},"tb_text":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/text.min.css","v":"7.1.1"},"theme-style":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/style.min.css","v":"7.1.1"},"themify-media-queries":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/media-queries.min.css","v":"7.1.1","m":"(max-width:1200px)"}}