Still wondering whether to choose Office 2019 or Office 365?

Eduard Plesu

Azure Business Development Manager & Microsoft Certified Trainer

Still wondering whether to choose Office 2019 or Office 365 as the productivity solution for your company?

 If you are looking for a service that is always improving, adding new tools, features, security updates, to ensures you have the most up-to-date modern productivity tools, there is only one answer; Office 365.

For a one-time purchase cost, Office 2019 gives you security updates, but you won’t get any new upgrades or features, like the acclaimed new “XLOOKUP function”, only available for Office 365 users.


Source: Compare Office 365 subscription with Office 2019 (one-time purchase)

The “XLOOKUP function” searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

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

[match_mode]

Specify the match type:

Optional

0 - Exact match. If none found, return #N/A. This is the default.
-1 - Exact match. If none found, return the next smaller item.
1 - Exact match. If none found, return the next larger item.
2 - A wildcard match where *, ?, and ~ have special meaning.

[search_mode]

Specify the search mode to use:

Optional

1 - Perform a search starting at the first item. This is the default.
-1 - Perform a reverse search starting at the last item.
2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.
-2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

You can find more details and examples about the new Excel features at the following link: https://support.office.com/en-us/article/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929

Here is an example I played with:

 If you want to try this function and don’t have Office 365 yet, contact your Microsoft Partner to ask for a trial version.