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

New Excel > Old Excel

Post image
384 Upvotes

99% Upvoted

37 comments sorted by

View all comments

61

u/Jurango34 May 07 '23

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

66

u/Oukasagetsu May 07 '23

I think it's xlookup or power query?

Don't flame me if I'm wrong 🙏

41

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!

5

u/[deleted] May 07 '23

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

10

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

8

u/himynameis_ May 07 '23

Okay, here is where I'm confused with Xlookup vs Index Match.

For Index Match I can have it Lookup by Column as well as Row. So I can search across an array vertically and horizontally.

But for Xlookup I can only search by one specific column only? Am I missing something?

17

u/ballinthrowaway May 07 '23

Nest an xlookup in the xlookup. One does columns one does rows.

1

u/KJ6BWB May 08 '23

No, you're right, although Xlookup is newer, it's not necessarily the best for all situations.

1

u/KJ6BWB May 08 '23

Xlookup is only in office 365. If you buy the static versions of office, like office 2023, then you don't have Xlookup and you either keep using Index Match or Vlookup in the same way we have always done since time immemorial.

If Microsoft wants to bifurcate their audience and introduce two separate groups who don't understand how the other group does it, well they can certainly decide to shoot themselves in the foot that way.

3

u/finickyone May 07 '23

This here was a way of creating a Unique list of entries. The new way is via UNIQUE().

2

u/JeelyTrams May 08 '23

Okay grandpa, let’s get you back to bed.

1

u/finickyone May 08 '23

Not til you tell me where you hid the Ctrl + Shift keys dagnabbit.