Related Posts
Can anyone help me with a referral for a fraud/risk analyst role in Barclays. I am a Bcom graduate with hands on experience in the BPO/KPO industry and currently pursuing a distance MBA in finance. Proficient in advanced Excel and SQL. Any lead would be really helpful. Thanks in advance Barclays
“Let me just play devils advocate here…”
Additional Posts in Excel Genius
This bowl is so dry lol
New to Fishbowl?
unlock all discussions on Fishbowl.
Hate to be the one splitting hairs but Index/Match is technically more efficient than XLookup from purely a performance standpoint.
Apologies for being “that guy” - I’ll see myself out. 🚪
Thanks for having my back, K3. 🤝
Hey D1 - have a nice a weekend and try not to hurt yourself. If this sounds confusing, I can only encourage you to give Google’s search engine a whirl - it’s pretty slick once you familiarize yourself with it!
Make sure all of the end users have Office 365 because they won't be able to receive files with XLOOKUP otherwise.
Sorry Mr. Auditor, you’re not cool enough for my formulas 😎
Subject Expert
Yes, =XLOOKUP() is just a more versatile function then Vlookup, and it does combine some index match benefits (but not all). It comes with a compatibility issue, i.e., it only works on office 365 as of now
A couple of comments:
1. Xlookup is ~30% less efficient than index match, and index match is ~8% worse than Vlookup. Caveat: if you use binary search for Xlookup, it will be slightly better than index match
2. Xlookup, as Vlookup, cannot perform optimized search for 2D arrays
3. Personal preference: I still mainly use index match due to versatility, only uses double vlookup when performance is a big concern (double vlookup trick can be much much faster)
Wowww, thanks I’m going to try that
Only available in O365...
I have 365. I’ve never used it until today really and I’m just learning but it’s pretty damn beautiful. I am worried about compatibility though….although everyone at my company is going to be on 365 so I can’t imagine there’s a problem
Backwards compatibility has entered the chat...
It’s great, but because of compatibility concerns with my clients, I still use INDEX MATCH almost exclusively.
Beats Index Match for sure. Easier to deal with and if you use more than two lookup values the formula is easier than if you were to do it with index match. With that said, it can be extremely slow.
Yeah it definitely doesn't beat it. Just learn how to use index/match fully (as above match(1...) for multiple criteria; and also INDEX(MATCH(),MATCH()) for 2D array
It's basic stuff and will serve you well over the long term
Look at the breakdown of the formulae for XLOOKUP and INDEX(MATCH()). From an end-user perspective, it's basically the same thing, but introduced for people whose heads explode when you have to nest.
If the XLOOKUP excites you, you need to learn more Excel...
It is basically the same thing, but it’s a cool new function that can eventually eliminate v lookup and h lookup with the kicker that it can do index match. Microsoft themselves even want to phase out the old functions. It’s okay to learn new things 🤣