LinkBack URL
About LinkBacks
As an Office Insider, I now have two new functions: XLOOKUP and XMATCH.
These look really useful. Take a look here: https://techcommunity.microsoft.com/...UP/ba-p/811376
As an Office Insider, I now have two new functions: XLOOKUP and XMATCH.
These look really useful. Take a look here: https://techcommunity.microsoft.com/...UP/ba-p/811376
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I read an article on it, and it looks really cool and useful!
(I especially like the wildcard match feature, where you can search based on a partial string!)
Unfortunately my office is not upgrading to Office 365 for the foreseeable future, so I'll probably never get to use it. Are there any homemade UDFs out there that replicate XLookup?
Same as above, we wont be getting it anytime soon, although it does look useful, The searching in reverse and the having a column returned from the left of the column the match is made on seemed useful. We did have hopes it might replace an index, match, match formula before we read the article, but alas this is not the case!
FWIW, you can already use wildcards with VLOOKUP or MATCH, as long as you're using exact match (perhaps a little confusingly).
Rory
Yes you are right, I didn't know that. I just tried it and it actually worked.
A couple of observations -- noting that I am not an Office Insider, so I cannot actually use these functions (and probably won't until LibreOffice and others implement them).
1) XLOOKUP() -- unlike all previous lookup functions -- defaults to exact match (the most common option) rather than approximate match.
2) As before, the examples are all for the "exact match" option, which makes it more difficult for users to learn how to use the approximate match options.
3) If you choose to use the approximate match options, your lookup table must still be sorted in ascending or descending order (a separate step outside of the lookup function).
4) I am wondering how XLOOKUP()'s performance compares to VLOOKUP()/HLOOKUP(). It is somewhat common thing here -- especially when a user comes here complaining of a slow spreadsheet -- to find that lookups are a big part of the spreadsheet -- especially when those lookups are "exact match" linear lookups. I wonder if XLOOKUP() will be able to perform lookups faster, or if its performance is going to be similar to VLOOKUP/HLOOKUP. If it is not substantially faster, I could see situations where INDEX() and MATCH() (in separate cells) could be preferable where you perform the slow lookup with MATCH() in one cell, then return multiple values based on the one lookup. Maybe XLOOKUP() will not completely replace these other functions?
Just some thoughts. As noted, I probably won't ever get to use it in Excel, so maybe it doesn't matter.
Originally Posted by shg
They'd already made performance improvements to the lookup functions (see here) so I'd guess those are incorporated into the new functions.
XLookup also has a feature that it can search from bottom to top, however the few times I needed to do that I would just sort the list the way I wanted before doing the VLookup.
I always think that if I need to use a lookup / match function, then I should really be using Power Query / Power Pivot instead...
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Great response, guys - really positive!!! LOL!
I shall be giving them a whirl when they arrive in Office 365 at work.
They are available in 365.
if i have some porridge i can share a spoon or two. Soft is another matter. i can't work with those functions in 2016 with a file, created in 365. I even can't bye a small adding for say $1 or less. it is not for customers. Thomas Jefferson was right:
If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it. Its peculiar character, too, is that no one possesses the less, because every other possesses the whole of it. He who receives an idea from me, receives instruction himself without lessening mine; as he who lights his taper at mine, receives light without darkening me. That ideas should freely spread from one to another over the globe, for the moral and mutual instruction of man, and improvement of his condition, seems to have been peculiarly and benevolently designed by nature, when she made them … incapable of confinement or exclusive appropriation.
Are you sure that you aren't in the "Insider" program as xlookup isn't available in my regular version yet?
If my solution worked (or not) please let me know. If your question is answered then please remember to mark it solved
Computers are like air conditioners. They work fine until you start opening windows. ~Author Unknown
Scroll down, Tim, and read the note. It is still not generally available.
Obviously Tim didn't think I knew what functions my version of Excel has
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks