Wait … xlookup is basically VLookup and Index/Match?

Is this seriously a viable alternative because what the hell have I been doing with my life?

likefunnysmart
Posting as :
works at
You are currently posting as works at

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. 🚪

likefunny

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.

like

Sorry Mr. Auditor, you’re not cool enough for my formulas 😎

likefunny

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)

like

Wowww, thanks I’m going to try that

Only available in O365...

like

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

like

Backwards compatibility has entered the chat...

like

It’s great, but because of compatibility concerns with my clients, I still use INDEX MATCH almost exclusively.

like

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 🤣

Post Photo

Related Posts

How is Optum in growth salary wlb leaves for QA tester? #optum#cognizant#Optum Tata Consultancy IBM Fiserv, Inc Wipro Amazon

like

Voice your thoughts about internal audit leadership and passion!

like

Does anyone have great chair recommendations. I'm young and don't want to completely ruin my back while working.

uplifting

Hi Everyone,

Please share how you save income tax.

I know 80c and HRA

likehelpful

Please suggest some product base company with good work life balance, which also hire from tier 3.
I am aware about some companies like:
Cisco
Microsoft
Qualcomm
Linkdin
Attlasian
Walmart
I want to target this companies in near future. Want to increase this list. Correct me if I am wrong somewhere.

like

Since WFH I gained at least 7 pounds so decided to take actions. Is it true that weight loss is 80% food and 20% excise ? If my body burns 1,300 calories daily and I eat 1,300 daily, can I ....

like

Anyone using Fountas and pinnell reading curriculum?

helpful

I am trying to be intentional with the order of my instruction for my reading standards and grammar standards next year. Any scope and sequence suggestions or examples?

like

What are your favorites research websites for grades 4-5?

like

We have ordered books from Scholastic for many years, but I was recently approached by an Usborne consultant. Does anyone have an opinion and/or can compare the two publishers? Thanks!

like

Just moved here. Is there a local Packers bar or go-to place for cheeseheads 🧀🏈?

like

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

like

Hi

I have one major concern regarding the pakage structure what America express offer. Is it like they will compare with the last pakage and offer you on that basis or else they are bounded by a standard pakage bracket in between that bracket they will make an offer?

like

Hi fishes,

I am looking for an HRBP role in good MNCs in Bangalore. I have overall 3.5 years of corporate HR experience and currently an HRBP.
Any leads will be highly appreciated as I am actively looking for a change.

like

I think I’m mildly depressed. My friends don’t care, my family doesn’t care, do I even care?

like

Women with hypothyroidism: what symptoms did you experience before diagnosis?

like

I’m not diagnosed but I find it so hard to get things done. I get distracted when I’m about to note down what I need to do, then I would forget that I wanted to note down the to-do. Then it takes me forever to get to that to-do and when I finally decide to get to the to-do, I get distracted all the time and potentially forget about it again. Sometimes caffeine can help with deadline approaching but at other times it just makes me hyper but not focused. I found my life stagnant for years. Advice?

like

Hi ! What do you think could be a great/honest salary for a Sales engineer role, based on EMEA (Netherlands) with 3+ years of experience.? The company is a famous company of Kubernetes ecosystem.

like

I am in a rut...I'm thinking about ending 2022 and starting 2023 on a high note. I have been thinking about taking a last-minute trip. Where would be a great place to celebrate the new year?

like

“Let me just play devils advocate here…”

Post Photo
funnylikeuplifting

Additional Posts in Excel Genius

Anyone have ideas for somewhere where I can practice excel interviews? I have a 1-hr excel case interview coming up with one of the FAANGs for a product ops / strategy role. I’ll be given a data set and will be asked to work through a few questions with the interviewer.

like

I’m sure there is a simple answer to this but it’s been a long day already and my brain is toast. I have a column of dates (for the past 3 years) and I want to create a formula that counts the number of non-blank cells for a specific date. I’ve tried COUTIFS but it includes the blank cells . Thinking there needs to be a COUNTA formula nested in there somewhere but not sure how to do the conditional part (specific date). Any help is greatly appreciated.

like

What other function can I use for a multi criteria sum aside sumifs?

like

Has anyone ever had issues with formulas not automatically updating when working in an excel workbook on sharepoint? Ive confirmed that the formulas are set to calculate automatically. I have to manually go in an click “calculate now” every time I change something, but it only updates in my view of the worksheet.

like

Cleverness is coming up with a way to handle every edge-case using multiple nested formulas in a single cell.

Wisdom is structuring your model so that you don’t need those nested formulas in the first place.

I really f*$&ing need to stop being clever.

like

Does anyone know of a good excel training online (pivot tables…filters…) ? (Preferably free)

like

Can someone please explain what the below formula is doing?
=IFERROR(INDEX(ProjectList,MATCH(1,COUNTIF([@PO Line Short Text]],”*”&ProjectList&”*”),0)),””)

like

How can I track a pivot chart to its origin pivot. Have a ton of charts and pivots, so trying to find an easy way to see which chart links to which pivot. Thanks in advance.

like

Can somebody help me with the IF formula here please? I want to match Column K and Column O with No as the select values. I’ve tried =IF(K6=O6=“No”, ”match”) but it didn’t work. Thanks in advance for your help!

This bowl is so dry lol

likefunny

Hi!! Would anybody know which formula I could use I stead of VLOOKUP that would return multiple finds and not just the first one?
I appreciate your help.

like

Hi I need help please! Trying to sumifs with one of the criteria being a blank cell. Problem is the cell is not truly blank as it contains a formula that is producing the blank cell. How can I get around this to get my proper summed results???

like

I have a transaction report SUN systems one and want to transfer data ( the actuals) into a another excel file I have to track budget versus actuals. Anyone know of any formulas? Instead of manually searching for relevant line to post data

like

Do pivot tables sometimes cut text if the cell is long and how to mitigate that?

like

Anyone familiar with the fuzzy match add in, and if so, how do you run a fuzzy match within one column of information rather than two tables? I.e., I want to find close matches within one list of vendor names

likesmart

Your favorite formula (or combination) that you get surprised someone didn’t know. E.g I like using SUMIFs and surprised people still use SUMIF. Also SUMPRODUCT another favorite one of mine.

like

My firm pushed an update to O365 and now a number of custom Excel add-ins no longer work. Has anyone come across a fix for this or is it an unresolvable compatibility issue?

like

Seeking advice. I have a table of controls with multiple repeating instances. I would like to collect the avg of repeating instances and output them to different table.

Post Photo
like

Recommendations for selecting an entire row based (on a 3 unique identifiers located in the first 3 columns) and then exporting said row either into csv or throw into a new sheet to then generate visuals.

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Scan your QR code to download
Fishbowl app on your mobile

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal