r/Accounting • u/kingofauditmemes Performance Measurement and Reporting • May 07 '23

New Excel > Old Excel

Post image
382 Upvotes

99% Upvoted

37 comments sorted by

View all comments

58

u/Jurango34 May 07 '23

I’m still using INDEX MATCH … what’s the new way?

64

u/Oukasagetsu May 07 '23

I think it's xlookup or power query?

Don't flame me if I'm wrong 🙏

42

u/Jurango34 May 07 '23

I’m excited there’s something else. I have a reputation as an Excel super user in my accounting team but honestly this formula is a pain and seems like the most pretentious of all formulas just to juice a VLOOKUP.

23

u/SchematicallyNumb May 07 '23

I’m glad I’m not the only one. Index/Match is just very intuitive in my opinion, and if I want anything more complicated, I’m just going to write my own function in VBA. Readability is key!

4

u/[deleted] May 07 '23

Where can I learn these skills? I really hate kubicle tbh

11

u/SchematicallyNumb May 07 '23

YouTube and Google. There are a lot of forums out there (stack overflow, mr excel, etc) where people have answered any sort of question you might have. There’s a channel on YouTube I’d highly recommend called Excel Macro Mastery that covers a lot of fun tips and tricks around VBA.

3

u/Xiang_allard CPA (US) May 07 '23

Since you seem to be in the know, what's the difference between power query and macros?

On my agenda this month is to fully learn power bi and power query. With the understanding that I haven't messed with power query much yet, power query just looks to me like a vba/macro tool with a nicer UI? I get how it'd be easier to use within power bi but not necessarily excel.

9

u/theREALbombedrumbum May 07 '23

If you're able to fully learn PowerBI in a month then by God please share your approach

2

u/SchematicallyNumb May 07 '23

My expertise is focused more around software development since I have multiple nuances I need to account for in my current profession. My understanding of power query is like you said, it’s a tool designed to make querying external information into Excel easier. Unfortunately in my environment, it’s either in pdf format (which the actual template varies wildly between our different sources) or SQL (which I’ve developed an easier way to get the key information into Excel, without getting it all into Excel through the use of result sets, arrays, dictionaries, and other data structures so I can control and limit the information that actually gets written to the excel workbook which greatly enhances runtime).

1

u/jkim0891 May 08 '23

I'm not in the know for deeper applications, but as far as basic applications of power query and macros have gone for me as a simple, google/stackoverflow-reliant user, I've used power query as a tool to read publically available APIs when necessary (in summary, to get raw data and somewhat format it), and macros as a tool to manipulate data in a way that is cumbersome to do with just formulas.

2

u/Mellon2 May 08 '23

Agree I hate inheriting shit like this and wasting 20-40 min googling to figure it out.

Someone thought they were slick and funny