Windows 10: Excel XLOOKUP function is now generally available

Brink · Feb 11, 2020

The highly anticipated XLOOKUP function in Excel is generally available for all users across Windows, Mac, and Excel on the web. XLOOKUP is the successor to the iconic VLOOKUP function, which has been one of the most used functions in Excel.

XLOOKUP helps users find what they need more efficiently with fewer limitations, from being able to look up a value vertically and horizontally (and to the left!) to supporting column insertions and deletions and more.

Previously, we released XLOOKUP to Office Insiders and received lots of excitement and feedback on the feature. While you can still perform VLOOKUP functions, we encourage you to try XLOOKUP for better and faster results. For more information on XLOOKUP, read our help article and our previous announcement of XLOOKUP’s Office Insiders release.
Click to expand...

Source: https://www.microsoft.com/en-us/micr...lly-available/

Tweet

— Twitter API (@user) View on Twitter
Click to expand...

:)

Brink · Feb 11, 2020

Announcing XLOOKUP for Excel in Office 365

I am excited to announce XLOOKUP; the successor to iconic VLOOKUP function. Before I describe XLOOKUP, I want to take a moment to reflect on VLOOKUP's incredible history and impact on spreadsheeting.

VLOOKUP has been with Excel from the beginning; it was included in Excel 1 for Macintosh released in 1985. For 34 years, VLOOKUP has been the first lookup function learned by Excel users and our 3rd most used function (after SUM and AVERAGE). In fact, it’s so fundamental to spreadsheeting that most users can recall the sense of achievement they felt when they first mastered VLOOKUP, myself included.

Introducing XLOOKUP

XLOOKUP is named for its ability to look both vertically and horizontally (yes it replaces HLOOKUP too!). In its simplest form, XLOOKUP needs just 3 arguments to perform the most common exact lookup (one fewer than VLOOKUP). Let’s consider its signature in the simplest form:

XLOOKUP(lookup_value,lookup_array,return_array)

lookup_value: What you are looking for

lookup_array: Where to find it

return_array: What to return

Let's see it in action:

Advanced XLOOKUP variations

To perform advanced lookups, you can use XLOOKUP’s optional 4th and 5th mode arguments: match_mode and search_mode.

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

match_mode allows you to set the type of match you’d like to perform. The options are:

Use zero to perform an exact match, this is the default.
Use 1 or -1 to allow a match against the nearest smaller (or larger) item when there is no exact match.
Use 2 to do a simple wildcard match where ? means match any character and * means match any run of characters.

search_mode lets you configure the type and direction of search. The options are:

Use 1 or -1 to search from first-to-last or last-to-first.
Use 2 or -2 to do a binary search on sorted data. This is included for expert users only.

Why release a new lookup function?

While VLOOKUP was widely used, it has several well-known limitations which XLOOKUP overcomes:

Defaults to an “approximate” match: Most often users want an exact match, but this is not VLOOKUP’s default behavior. To perform an exact match, you need to set the 4th argument to FALSE. If you forget (which is easy to do), you’ll probably get the wrong answer.

Does not support column insertions/deletions: VLOOKUP’s 3rd argument is the column number you’d like returned. Because this is a number, if you insert or delete a column you need to increment or decrement the column number inside the VLOOKUP.

Cannot look to the left: VLOOKUP always searches the 1st column, then returns a column to the right. There is no way to return values from a column to the left, forcing users to rearrange their data.

Cannot search from the back: If you want to find the last occurrence, you need to reverse the order of your data.

Cannot search for next larger item: When performing an “approximate” match, only the next smaller item can be returned and only if correctly sorted.

References more cells than necessary: VLOOKUP 2nd argument, table_array, needs to stretch from the lookup column to the results column. As a result, it typically references more cells than it truly depends on. This could result in unnecessary calculations, reducing the performance of your spreadsheets.

What’s the future for VLOOKUP & HLOOKUP?

VLOOKUP and HLOOKUP will both continue to be supported by Excel. That said, we strongly recommend using XLOOKUP in favor of VLOOKUP and HLOOKUP because XLOOKUP is simpler to use and less prone to error.

XMATCH

In addition to XLOOKUP we are also launching XMATCH which has a similar signature to XLOOKUP but returns the index of the matching item. XMATCH is both easier to use and more capable than its predecessor MATCH.

XMATCH(lookup_value,lookup_array,[match_mode],[search_mode])

Learn More

You can learn more about XLOOKUP and XMATCH from these resources:

XLOOKUP help

XMATCH help

Availability Notes

XLOOKUP and XMATCH are available for users signed up for the Office 365 Insiders Program starting today and will continue rolling out to Insiders over the next few weeks. Gradual roll outs allow us to gather feedback and ensure feature quality.

To stay connected to Excel and its community, read the Excel blog posts and send us ideas and suggestions via UserVoice. You can also follow Excel on Facebook and Twitter.

Joe McDaid (@jjmcdaid)

Program Manager, Excel
Click to expand...

Source: https://techcommunity.microsoft.com/...UP/ba-p/811376

Brink · Feb 11, 2020

Announcing XLOOKUP for Excel in Office 365

I am excited to announce XLOOKUP; the successor to iconic VLOOKUP function. Before I describe XLOOKUP, I want to take a moment to reflect on VLOOKUP's incredible history and impact on spreadsheeting.

VLOOKUP has been with Excel from the beginning; it was included in Excel 1 for Macintosh released in 1985. For 34 years, VLOOKUP has been the first lookup function learned by Excel users and our 3rd most used function (after SUM and AVERAGE). In fact, it’s so fundamental to spreadsheeting that most users can recall the sense of achievement they felt when they first mastered VLOOKUP, myself included.

Introducing XLOOKUP

XLOOKUP is named for its ability to look both vertically and horizontally (yes it replaces HLOOKUP too!). In its simplest form, XLOOKUP needs just 3 arguments to perform the most common exact lookup (one fewer than VLOOKUP). Let’s consider its signature in the simplest form:

XLOOKUP(lookup_value,lookup_array,return_array)

lookup_value: What you are looking for

lookup_array: Where to find it

return_array: What to return

Let's see it in action:

Advanced XLOOKUP variations

To perform advanced lookups, you can use XLOOKUP’s optional 4th and 5th mode arguments: match_mode and search_mode.

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

match_mode allows you to set the type of match you’d like to perform. The options are:

Use zero to perform an exact match, this is the default.
Use 1 or -1 to allow a match against the nearest smaller (or larger) item when there is no exact match.
Use 2 to do a simple wildcard match where ? means match any character and * means match any run of characters.

search_mode lets you configure the type and direction of search. The options are:

Use 1 or -1 to search from first-to-last or last-to-first.
Use 2 or -2 to do a binary search on sorted data. This is included for expert users only.

Why release a new lookup function?

While VLOOKUP was widely used, it has several well-known limitations which XLOOKUP overcomes:

Defaults to an “approximate” match: Most often users want an exact match, but this is not VLOOKUP’s default behavior. To perform an exact match, you need to set the 4th argument to FALSE. If you forget (which is easy to do), you’ll probably get the wrong answer.

Does not support column insertions/deletions: VLOOKUP’s 3rd argument is the column number you’d like returned. Because this is a number, if you insert or delete a column you need to increment or decrement the column number inside the VLOOKUP.

Cannot look to the left: VLOOKUP always searches the 1st column, then returns a column to the right. There is no way to return values from a column to the left, forcing users to rearrange their data.

Cannot search from the back: If you want to find the last occurrence, you need to reverse the order of your data.

Cannot search for next larger item: When performing an “approximate” match, only the next smaller item can be returned and only if correctly sorted.

References more cells than necessary: VLOOKUP 2nd argument, table_array, needs to stretch from the lookup column to the results column. As a result, it typically references more cells than it truly depends on. This could result in unnecessary calculations, reducing the performance of your spreadsheets.

What’s the future for VLOOKUP & HLOOKUP?

VLOOKUP and HLOOKUP will both continue to be supported by Excel. That said, we strongly recommend using XLOOKUP in favor of VLOOKUP and HLOOKUP because XLOOKUP is simpler to use and less prone to error.

XMATCH

In addition to XLOOKUP we are also launching XMATCH which has a similar signature to XLOOKUP but returns the index of the matching item. XMATCH is both easier to use and more capable than its predecessor MATCH.

XMATCH(lookup_value,lookup_array,[match_mode],[search_mode])

Learn More

You can learn more about XLOOKUP and XMATCH from these resources:

XLOOKUP help

XMATCH help

Availability Notes

XLOOKUP and XMATCH are available for users signed up for the Office 365 Insiders Program starting today and will continue rolling out to Insiders over the next few weeks. Gradual roll outs allow us to gather feedback and ensure feature quality.

To stay connected to Excel and its community, read the Excel blog posts and send us ideas and suggestions via UserVoice. You can also follow Excel on Facebook and Twitter.

Joe McDaid (@jjmcdaid)

Program Manager, Excel
Click to expand...

Source: https://techcommunity.microsoft.com/...UP/ba-p/811376

Raymond_M · Feb 11, 2020

Missing function like data validation function in excel sheet accessed in SkyDrive

Hi niharbiswas,

Welcome to the Microsoft Community forum. I'm here to assist you. I'd need more information from you for investigation. Please answer the following:

Where are you opening the excel sheet? Is it on your phone's browser or on the SkyDrive app?

What is your phone exact model, OS and OS version?

Does the problem happen on all your excel sheets including files uploaded from a computer and newly created ones?

Does the problem happen if you access the excel sheet on the Excel client a computer?

We look forward to your reply.

Note: Some functions and features in Office are not available for Office Web Apps access. This includes data validation function.

Thank you!

Windows 10: Excel XLOOKUP function is now generally available

Excel XLOOKUP function is now generally available

Excel XLOOKUP function is now generally available

Excel XLOOKUP function is now generally available

Excel XLOOKUP function is now generally available - Similar Threads - Excel XLOOKUP function

The "Help" function is not always available from Excel.

The "Help" function is not always available from Excel.

Add to OneDrive is now generally available

PowerPoint Live is now generally available

Cards View in Excel now generally available on Android and iOS

Announcing XLOOKUP for Excel in Office 365

Visual Studio 2019 now generally available

New Gmail now generally available

#AzureAD PowerShell V2.0 is now Generally Available

Users found this page by searching for:

xlookup missing

when did excel xlookup become available