Excel - Arrival of XLOOKUP

Questions on how we spend our money and our time - consumer goods and services, home and vehicle, leisure and recreational activities
Post Reply
Topic Author
ubermax
Posts: 1675
Joined: Tue Feb 11, 2014 2:19 pm
Location: Connecticut

Excel - Arrival of XLOOKUP

Post by ubermax »

Microsoft will apparently be releasing a new Excel function , XLOOKUP, to Office 365 users in the near future and to other users , e.g. Office Pro in the coming weeks - does anyone know how existing users will get this Excel update ?
Jack FFR1846
Posts: 12829
Joined: Tue Dec 31, 2013 7:05 am
Location: 26 miles, 385 yards west of Copley Square

Re: Excel - Arrival of XLOOKUP

Post by Jack FFR1846 »

It's already bugging me when I open up Excel. Hitting X fixed that problem.
Bogle: Smart Beta is stupid
greener
Posts: 2
Joined: Tue Sep 03, 2019 9:41 pm

Re: Excel - Arrival of XLOOKUP

Post by greener »

Hi,
Based on the podcast "Windows Weekly" Microsoft could consider to limit a number of features in a single purchase license office vs. a subscription Office 365.
In the longterm, Office with a perpetual (single purchase) license will be discontinued.
Texanbybirth
Posts: 1398
Joined: Tue Apr 14, 2015 12:07 pm

Re: Excel - Arrival of XLOOKUP

Post by Texanbybirth »

Per Microsoft's function support webpage for this function:
Note: August 28, 2019: XLOOKUP is currently a beta feature, and only available to a portion of Office Insiders at this time. We'll continue to optimize it over the next several months. When XLOOKUP is ready, we'll release it to all Office Insiders, and Office 365 subscribers.
As an "Index/Match" guy, I'm excited to try this function out. It'll enhance the user experience, as a lot of people are confused by "Index/Match". It'll also keep me gainfully employed for a couple more years as we have hundreds of templates that will slowly need to be converted from VLOOKUP to XLOOKUP. :D
“The strong cannot be brave. Only the weak can be brave; and yet again, in practice, only those who can be brave can be trusted, in time of doubt, to be strong.“ - GK Chesterton
User avatar
batpot
Posts: 1208
Joined: Thu Jul 11, 2013 8:48 pm

Re: Excel - Arrival of XLOOKUP

Post by batpot »

greener wrote: Tue Sep 03, 2019 9:49 pm Hi,
Based on the podcast "Windows Weekly" Microsoft could consider to limit a number of features in a single purchase license office vs. a subscription Office 365.
In the longterm, Office with a perpetual (single purchase) license will be discontinued.
Yes, I understand this.
My post got deleted, but I'm frustrated with Microsoft going to this model after ~30 years of buy once, particularly when the annual price is so extreme.
Topic Author
ubermax
Posts: 1675
Joined: Tue Feb 11, 2014 2:19 pm
Location: Connecticut

Re: Excel - Arrival of XLOOKUP

Post by ubermax »

I purchased Office pro with my laptop in 2017 , so when this function is globally released will I have to buy/download the new version or would I be able to add this function to my existing version/license ?
User avatar
RootSki
Posts: 515
Joined: Mon Feb 20, 2017 11:52 am

Re: Excel - Arrival of XLOOKUP

Post by RootSki »

I love the VLOOKUP function. It’s saved me countless hours of time, while my coworkers collate data manually. What’s the difference between them?
Texanbybirth
Posts: 1398
Joined: Tue Apr 14, 2015 12:07 pm

Re: Excel - Arrival of XLOOKUP

Post by Texanbybirth »

RootSki wrote: Wed Sep 04, 2019 2:49 pm I love the VLOOKUP function. It’s saved me countless hours of time, while my coworkers collate data manually. What’s the difference between them?
There is a love-hate relationship. People love its power. However, vlookup (hlookup) can only look to the RIGHT (downward?) of the column (row) you're using to search, so your tables have to be formatted properly to accommodate this. It has long been a source of consternation for those who don't want to take the time to manually (or through a macro) organize their tables. "INDEX/MATCH" is a workaround for this, though it's a cumbersome formula for some to interpret, and the criteria for MATCH-ing (the third argument in the function) have left some users wanting. (I've always used it flawlessly.) It's also a lot more difficult for people to interpret your formula if you get hit by a bus. :beer
“The strong cannot be brave. Only the weak can be brave; and yet again, in practice, only those who can be brave can be trusted, in time of doubt, to be strong.“ - GK Chesterton
User avatar
RootSki
Posts: 515
Joined: Mon Feb 20, 2017 11:52 am

Re: Excel - Arrival of XLOOKUP

Post by RootSki »

Texanbybirth wrote: Wed Sep 04, 2019 2:53 pm
RootSki wrote: Wed Sep 04, 2019 2:49 pm I love the VLOOKUP function. It’s saved me countless hours of time, while my coworkers collate data manually. What’s the difference between them?
There is a love-hate relationship. People love its power. However, vlookup (hlookup) can only look to the RIGHT (downward?) of the column (row) you're using to search, so your tables have to be formatted properly to accommodate this. It has long been a source of consternation for those who don't want to take the time to manually (or through a macro) organize their tables. "INDEX/MATCH" is a workaround for this, though it's a cumbersome formula for some to interpret, and the criteria for MATCH-ing (the third argument in the function) have left some users wanting. (I've always used it flawlessly.) It's also a lot more difficult for people to interpret your formula if you get hit by a bus. :beer
Pure love from me. I keep my tables organized and properly formatted, and if someone mucks it up, I'll change their access to read only. Office365 is great for telling you who made the last changes. When I go back to work next month, I'll take a look at the Index/Match function. Never heard of it before. Thanks.
Grt2bOutdoors
Posts: 23155
Joined: Thu Apr 05, 2007 8:20 pm
Location: New York

Re: Excel - Arrival of XLOOKUP

Post by Grt2bOutdoors »

Let me guess, you use XLOOKUP to mark the spot!
"One should invest based on their need, ability and willingness to take risk - Larry Swedroe" Asking Portfolio Questions
Texanbybirth
Posts: 1398
Joined: Tue Apr 14, 2015 12:07 pm

Re: Excel - Arrival of XLOOKUP

Post by Texanbybirth »

RootSki wrote: Wed Sep 04, 2019 3:26 pm
Texanbybirth wrote: Wed Sep 04, 2019 2:53 pm
RootSki wrote: Wed Sep 04, 2019 2:49 pm I love the VLOOKUP function. It’s saved me countless hours of time, while my coworkers collate data manually. What’s the difference between them?
There is a love-hate relationship. People love its power. However, vlookup (hlookup) can only look to the RIGHT (downward?) of the column (row) you're using to search, so your tables have to be formatted properly to accommodate this. It has long been a source of consternation for those who don't want to take the time to manually (or through a macro) organize their tables. "INDEX/MATCH" is a workaround for this, though it's a cumbersome formula for some to interpret, and the criteria for MATCH-ing (the third argument in the function) have left some users wanting. (I've always used it flawlessly.) It's also a lot more difficult for people to interpret your formula if you get hit by a bus. :beer
Pure love from me. I keep my tables organized and properly formatted, and if someone mucks it up, I'll change their access to read only. Office365 is great for telling you who made the last changes. When I go back to work next month, I'll take a look at the Index/Match function. Never heard of it before. Thanks.
If you only work with spreadsheets from people over whom you exert the necessary control to bend their table-formatting-wills, then you wield more power than me. Our data is from literally hundreds of sources, and I'm too lazy to worry about formatting it all in the ETL process. :D

Index/Match is technically a combination of two Excel functions. Here's a little write-up on the combo, for your reading pleasure.

:beer
“The strong cannot be brave. Only the weak can be brave; and yet again, in practice, only those who can be brave can be trusted, in time of doubt, to be strong.“ - GK Chesterton
JBTX
Posts: 7127
Joined: Wed Jul 26, 2017 12:46 pm

Re: Excel - Arrival of XLOOKUP

Post by JBTX »

Index match is more powerful and flexible than vlookup:

- you don't have to designate your column with a hard number (or a reference to a number). This makes it much easier to copy and maintain a relative reference.
- it can be used to accomplish v or h lookup
- the lookup can be either to the right or the left. It doesn't have to be to the right like v lookup.

When I am modeling I try to use index match. But it is harder to remember. If I'm just trying to do a quick lookup on the fly I still use vlookup.
ohai
Posts: 1327
Joined: Wed Dec 27, 2017 2:10 pm

Re: Excel - Arrival of XLOOKUP

Post by ohai »

I always thought the VLOOKUP range and column index arguments were poorly designed. XLOOKUP's two range inputs make much more sense. It also makes sense to differentiate between VLOOKUP and HLOOKUP by range shape, thus eliminating the need for two kinds of formulas. XLOOKUP also gives you flexibility to dynamically rearrange data within the lookup range without manually specifying a new lookup index.

So, overall, this new function is superior to the old versions and should be welcomed enthusiastically.

XLOOKUP also seems to make Index-Match redundant and has more efficient syntax, making spreadsheets more readable and more tidy overall.

Unfortunately, Office 365 is not compatible with a wide range of enterprise programs such as those that I use daily. So, I suppose I will use this function only in the next offline version of Office.
Post Reply