SOLVED

XLOOKUP

Iron Contributor

I heard a new function XLOOKUP which is very powerful and replace the function of VLOOKUP. 

 

It works find at my home computer installing Office 365, but it cannot be recognized as a function in my school which is using Excel 2019. When I searched the web, it seems that XLOOKUP supports Office 365 and some mobile version of Excel but not Excel 2019. 

 

Is there any way to use XLOOKUP in Excel 2019? Or, should I restricted to use VLOOKUP only? Thanks a lot. 

3 Replies

@Man Fai Chan 

 

I can tell you that XLOOKUP only recently became available on my home computer (a MacBook Air with Office 365 subscription support); I have yet to use it. It IS more powerful than VLOOKUP, but the latter does continue to be available and still satisfies most needs (along with INDEX/MATCH and other venerable functions).

 

So while you may feel restricted--a feeling I can identify with still, since there are other capabilities I read about here but don't have--in the age of coronavirus, you have to agree that this is not the worst form of suffering.

best response confirmed by Man Fai Chan (Iron Contributor)
Solution

Yes absolutely, the XLOOKUP function is fantastic. It handles a lot of things in a single shot. e.g. the ability to return a complete set of array, no enforcing of comparing the leftmost column only etc. Yes it is only presently given in office 365 and excel for android. Many users of office 365 had to wait to get this function, not all were provided.  It seems like it was given for a test kind of thing.

In future , it is expected that it becomes a standard formula of all excel versions through updates. For time being if you are just interested , you can have a look at it here

https://gyankosh.net/msexcel/functions/office365/excel-function-xlookup/#gsc.tab=0 

Still you have to use VLOOKUP  or INDEX MATCH only as its not available. But I hope that it get available through an update in near future.

@Man Fai Chan 

I don't think it'll be on Excel 2019, perhaps on Excel 2022 or so.

All power of XLOOKUP is if using on dynamic arrays Excel. Otherwise yes, that's nice to have, but INDEX/MATCH provides practically the same functionality.  

1 best response

Accepted Solutions
best response confirmed by Man Fai Chan (Iron Contributor)
Solution

Yes absolutely, the XLOOKUP function is fantastic. It handles a lot of things in a single shot. e.g. the ability to return a complete set of array, no enforcing of comparing the leftmost column only etc. Yes it is only presently given in office 365 and excel for android. Many users of office 365 had to wait to get this function, not all were provided.  It seems like it was given for a test kind of thing.

In future , it is expected that it becomes a standard formula of all excel versions through updates. For time being if you are just interested , you can have a look at it here

https://gyankosh.net/msexcel/functions/office365/excel-function-xlookup/#gsc.tab=0 

Still you have to use VLOOKUP  or INDEX MATCH only as its not available. But I hope that it get available through an update in near future.

View solution in original post