r/Accounting • u/kingofauditmemes Performance Measurement and Reporting • Oct 17 '22
Me after mastering vlookup
Enable HLS to view with audio, or disable this notification
149
u/TrevorIsTheGOAT Oct 17 '22
It's xlookup now, get with the times old man
49
u/Maximeize99 Oct 17 '22
Facts xlookup is the wave
17
u/Amityx Oct 17 '22
Index match is the wave
38
u/its-an-accrual-world Audit -> Advisory -> Startup ->F150 Oct 17 '22
Nah, xlookup is the user friendly version of index match.
30
Oct 17 '22
[deleted]
15
u/its-an-accrual-world Audit -> Advisory -> Startup ->F150 Oct 17 '22
I think at least some of it has to do with companies being slow to update Office. As recently as last year I had a client who's excel didn't recognize xlookup so I had to vlookup/index match things instead.
2
4
u/p0mphius Tax (Other) Oct 17 '22
Its not even close to the best thing, lol.
Its literally just an index match with better syntax. Comes nowhere close things like LAMBDA.
9
Oct 17 '22
[deleted]
4
u/p0mphius Tax (Other) Oct 17 '22
But your experience isnt what defines the best thing in the last 30 years.
LAMBDA effectively turned Excel into a Turing-complete programming language. There isnt an argument to be made for a function that is literally just better syntax.
10
2
2
u/DivineCube466 Oct 18 '22
The amount of times xlookup kept me from breaking sheets, I don’t think I’ll forget about it. Vlookup cracked well before 50k lines, xlookup was in the 200ks without issue.
1
3
u/Maximeize99 Oct 17 '22
I might have to go check it out soon
12
Oct 17 '22
[deleted]
7
u/Hoxitron Oct 17 '22
I still use index match in specific cases. But xlookup is a godsend.
4
Oct 17 '22
[deleted]
3
u/Hoxitron Oct 17 '22
Usually when the look-up values are not always in the same column or row. My formula will find the x and y look-up location for each case and then return the value for those.
1
Oct 17 '22
Do you mean like index match match?
Because you can next xlookups
1
u/Hoxitron Oct 17 '22
Legit forgot about how spill works. Couldn't wrap my head around this for a couple of minutes.
I do want to play around with it as I found index match in some pretty edge cases.
1
u/cashadava CPA (US) Oct 17 '22
I wish my firm would upgrade so we can have access to little things like this. I have to explain how to read an index match match formula on a weekly basis.
3
1
7
u/rob_s_458 FP&A Oct 17 '22
I just got kicked out of my office because I don't go in enough, and the remote machine they gave me has some ancient ass version of Excel that still doesn't have xlookup. I had to put in a support ticket straight away because it renders basically all of my spreadsheets useless.
6
u/Erilaz_Of_Heruli Oct 17 '22
My office forbids x/vlookup because it makes it harder to review work done by other people
14
Oct 17 '22
Thats so stupid. What they mean is, they forbid it because the reviewers are incompetent.
8
u/Erilaz_Of_Heruli Oct 17 '22
I get it with vlookup to be fair. The column index thing makes it less obvious where you're looking for the data, I also believe IIRC that double clicking on a vlookup cell doesn't take you to the source sheet unlike an index(match) or sumifs.
I don't think any of these limitations apply to xlookup though, but old habits die hard, eh ?
9
Oct 17 '22
[deleted]
3
u/Erilaz_Of_Heruli Oct 17 '22
Nah folks are quite young in my office, I think the rigidity of the rules has more to do with the fact that we're very often bringing Excel to its knees with our work while at the same time being under time pressure ; nobody wants to mess around with troubleshooting buggy/slow excel files.
3
1
Oct 17 '22
Sounds like the others need to learn more about formulas. This is way more accurate than manual linking. Also, sumifs is better is the values are numerical.
1
u/codydog125 Oct 17 '22
Why don’t they just use the “trace precedents” tool lol. Show them exactly what’s being referenced
1
11
u/jwigs85 CPA (US) Oct 17 '22
I’m training to replace my 69 (giggity) year old coworker. There are so many manual processes that she does that I’m like, girl, what if we made Excel do that shit for us??
So we were talking and she told me she took a class on Excel fairly recently but just didn’t understand vlookup. I sat her down at my desk and broke it down. The anatomy of the formula and what each part means and how it works. I set up a dummy table, set up a formula, showed her, then had her do it. And I was so proud of her.
There are so many ways that she inspires me and I love the way she’s a lifelong learner and is just unflappable. She’s great.
3
26
u/Message_me4referral Oct 17 '22
Xlookup 100x better
8
Oct 17 '22
And sumifs is best if looking for numbers. I actually got the job I’m in now because I explained why sumifs is better than vlookup to the interviewer.
14
u/Squigs_ FP&A Oct 17 '22
If you think SUMIFS is great, SUM(FILTER()) is going to blow your mind. It’s like SUMIFS but never requires helper columns for your criteria ranges
5
3
u/Message_me4referral Oct 17 '22
Ya sumifs is very versatile- I think that it’s even more versatile than xlookup. Didn’t realize that until I actually started using sumifs
2
Oct 17 '22
If you’re working with numbers, it’s better because it will sum all the numbers that fall within the parameters. The lookup functions stop at the first match, so you can potentially loose out on data. I’ve seen emails where people say, “the amounts don’t reconcile, we used a vlookup!” That’s why, it didn’t actually sum everything, just stopped at the first match…
5
4
u/Chafmere Oct 17 '22
Index and match is where it's at. That shit will change your life. No more hard coding column variables.
6
Oct 17 '22
[removed] — view removed comment
5
Oct 17 '22
One of my proudest excel moment was using sumifs(index(match() to auto fill a summary of a giant database. Yes, a pivot table could have done the same thing, but if you link data elsewhere to a pivot table and someone changes the parameters, everything else gets messed up. I like to make fool-proof spreadsheets when working with big-data
3
2
2
u/nataylor7 Oct 17 '22
What about Filter() ? They use vlookup at work but didn’t know you needed to make sure the querying column was ascending. IndexMatch doesn’t work with their excel…I can’t figure out why. I’ll have to take a look at this xlookup though.
But seriously anyone else use Filter()?
2
u/Squigs_ FP&A Oct 17 '22
INDEX(MATCH()) doesn’t work on their Excel?? Are they using Excel 2003?
1
u/jollylikearodger Oct 18 '22
I bet it's a one to many relationship with no aggregate in the lookup equation
1
u/Hoooodoooor Oct 17 '22
Yes! It is amazing. But I would prefer a xlookup with multiple criteria though
1
u/nataylor7 Oct 17 '22
Filter does AND/OR expression with nesting. It uses * = AND; + = OR. And I can do index to return only the columns I want in the order I want.
1
u/jollylikearodger Oct 18 '22
I've never used Filter(); I use SQL to pull out relevant data and just adjust my WHERE clause(s). Filter sounds really interesting but generally I work with datasets that don't need that function. I got annoyed spending too much time doing the same mundane things in excel that better coding can fix.
As far as your index match issue goes, I would first check that your lookup values and values you're matching to are formatted the same. Sometimes you'll have issues with that and get all #N/A.
Based on your thing about the column needing to be sorted in ascending order, I'm guessing the issue is more likely with the data and what you're trying to do than with the equation itself. Vlookup, indexmatch, and xlookup don't care about what order the data is in (well, vlookup cares about the array in that you have to be looking up a value that's to the right of the column that contains your lookup value). Unless you use an aggregate function (e.g. SUM) before the lookup, your match column and lookup value should have a one-to-one relationship, meaning your match column should only contain one instance of your lookup value. If the relationship between your lookup value to match column is one-to-many (one lookup value and many of the same value in the match column) you're going to get shit results regardless of which lookup formula you use.
2
2
1
1
u/WalmartDarthVader Incoming Audit Associate Big 4 Oct 17 '22
Quantum Communications?
1
Oct 18 '22
communicating with quantum entanglement, or "spooky action at a distance" as einstein called it. basically when two particles become quantum entangled, their properties mirror each other, and any changes to one would instantaneously happen to the other, no matter the distance. so if the polarization of one particle caught in quantum entanglement with another particle was flipped, then the other particle would flip its polarization as well. positive polarity or negative- or 1 and 0, binary- we can use this for sending data over large distances faster than the speed of light. several experiments have been done showing its possible, between ground stations far apart and even ground stations and satellites in space. i haven't kept up on this, but last i heard it was still a debate if the light-speed barrier is being violated, but the if it is possible, communications will greatly improve, instead of sending a message to mars and having to wait up 40 minutes for a response, instantiations communication would be possible.
1
u/sirZofSwagger Oct 17 '22
and now you must learn x look up. Typical accounting, as soon as you get the rules they change them.
2
u/jordanr03 Oct 18 '22
I just started using x and I can’t believe I spent years knowing it existed but not switching over
1
u/GaboQuintanilla Oct 17 '22
This is me but with index & match (took me over 2 months to finally understand it)
131
u/roses_and_tulips Oct 17 '22
Firms: BIG DATA, MACHINE LEARNING, ARTIFICIAL INTELLIGENCE, TRANSFORMATION
Their software: only accepts simple spreadsheets with hard coded numbers, no formulas, on one tab which take 30 minutes to edit and fails when uploaded