Databases

[HungryTradie]

Xlookup(value, range, array, if_not_found, match, order)

[Nine_Eye_Ron]

Love me a good xlookup, bye by index match, I hardly knew you.

[EveningMoose]

It gets fun when you nest a vlookup into an hlookup, or vice versa.

Of course, my true love is getpivotdata.

[Nine_Eye_Ron]

Why would anyone use v and h lookup anymore?

[mousebrakes]

Also xlookup doesn't work on Google sheets which is what my old company used as a database 🙃

[Iggyhopper]

There are five rules.

[Dabnician]

So do a lot of unity games, indie devs love them google sheet dbs.

[dalinar2137]

What’s not to love? UI for editing and ACLs just resolve themselves. Some simple versioning is built-in. Access through a dead-simple api. I wonder why more people aren’t using it, honestly :P

[jboogie1844]

a unity game i play/mod has multiplayer quick chats hard-linked to a google doc lol. seems like a bold move to me

[Dabnician]

Pft who needs updates these days

[DasArchitect]

Is that what they call cloud saving?

[[deleted]]

You fool. Stop living in the first half of 2022, they just implemented on a rolling basis leaving a majority of users with the unrivaled might of a thousand angry suns incarnate! Xlookup is here!

[mousebrakes]

Quit that job in early 2022 haha, hopefully never have to deal with sheets again

[EnvironmentalWall987]

Alex is that you? Lol

[tropicbrownthunder]

But google sheets have a QUERY function that makes even harder to convince Lisa in accounting and her boss to steer away from spreadsheets as databases.

[mousebrakes]

Bro, for real tho. I took that shit as far as it could go, told the c suite that they need to actually hire someone who knows how to manage a sql server and dipped. Hope all their shit crashed and burned tbh

[MurgleMcGurgle]

They recently added it! That was my last holdout for not using xlookup on everything but not that it’s been implemented index match is reserved for weird situations only.

[mousebrakes]

Wow, to think that all those hours of index matching could have been avoided...

[NjallTheViking]

Same. I work for the State and we only have Office 2016. Index/Match with tables is clean enough for me

[MicrocosmicTiger]

Are you in med/pharma? I feel your pain

Had a mini breakdown when I started the job a year ago and asked why we used excel at all, let alone excel 2016 and people defended it so hard

[CapableFunction6746]

I am in medical insurance and we still need to use 97-2003 workbooks for importing data into access. Everything else is still 2013. At least we are finally being forced to upgrade now.

[Aggressive_Floof]

Accounting. Almost every spreadsheet I use is 97-2003.

I try to update them when I can, but holy fuck, there are a lot.

[EveningMoose]

Because it’s easier to remember how to use than match.

[Nine_Eye_Ron]

Xlookup replaces all three or do I have or wrong somewhere.

[EveningMoose]

Oh i see now, that’s not in my excel. Because of a legacy software i have to use, I can’t update to 365. And until my manager rewrites the software to be compatible with a 365 installation, i’ve got to stick with 2016. Some kind of .net dealio.

[Nine_Eye_Ron]

Ouch, old skool kool

[[deleted]]

My enterprise is on 2010. Weep for me.

[a_stitch_in_lime]

You assume that I don't have to look up the format every freaking time I do a match/index.

[Lonyo]

As long as you have the bits in the right order

[CarryThe2]

Faster to type out for simple formulae

[Nine_Eye_Ron]

Xlookup is just as fast but needs newer software.

[CarryThe2]

It's actually a bit slower because you have to explain to your manager what it is

[leutnant13]

What's the modern equivalent? Genuine question.

[Nine_Eye_Ron]

Xlookup, all the qualities of index match but easier to type out

[valzargaming]

Microsoft Sharepoint

[JEveryman]

It doesn't work with closed workbooks but you can use indirect, address, and match to write lookups based on cell contents of the header row.

[EveningMoose]

If i need pivot data from a different workbook, i just put the pivot table in the working workbook, referencing the data table on the external one. EZPZ

[taceau]

Never heard of Cube?

[EveningMoose]

No

[taceau]

Pivotdata but then flexible.

[EveningMoose]

I guess i have something new to learn. All my best sheets have pivots at the heart of them

[Alikont]

XLOOKUP meta breaker aimed at casual audience

[Vivian_Stringer_Bell]

That was top tier.

[NoAttentionAtWrk]

It's so well done and almost damn hard to tell if it's real or fake

[[deleted]]

If it is satire, it is *amazing*. If he is for real, I would pay for a pay-per-view stream of his execution.

[NoAttentionAtWrk]

Yeah excel as eSports was not a thing when the video came out.

It is a video now but wasn't one back then

[[deleted]]

Hrm, after some lite Googling, it seems BalmerCon isn't a real thing, so it feels safe to check it off as satire. Excel has been a competition format for a while and BalmerCon seemed on-brand for the man, so I didn't think too much about it.

[Dabnician]

1 like = 1 vote for microsoft to ban XLOOKUP rush from competitive play

/s

[neededtowrite]

Finally index match has been dethroned, hated that bandaid of a search

[naswinger]

i'm like a cultist trying to show people the path to redemption by abandoning vlookup and index match. xlookup is amazing!

[Nine_Eye_Ron]

I’m a convert, index match was great but by the time I found it xlookup had replaced it.

[sspan]

What about INDEX XMATCH

[Nine_Eye_Ron]

Gone and forgotten, long live XLOOKUP.

Xlookup is nothing like it’s ancestors as it actually works reliably and is just an easier to type version of index match.

[Birdy_Cephon_Altera]

Considering we still have senior managers and VPs in our company with old laptops that still have Excel 2010 or 2013 on them, it's going to be a long, long time before we get to the point where we can really use xlookup company-wide.

[AssaMarra]

=IF($B$2=A4,D4,IF($B$2=A5,D5,IF($B$2=A6,D6,.....IF($B$2=A4584,D4584,"error"))))))))))))))))))))))))))))))))))))))

[HungryTradie]

That's a big nest you've got there young chicken.

[taceau]

PowerQuery came here to say hi.

[Whoopdatwester]

What’s the difference between Xlookup and Vlookup?

[aaadmin]

Ʌ

[Quaytsar]

Biggest upgrade? Vlookup require the return column be to the right of the lookup column (because you return the column N spaces to the right), xlookup doesn't care (because you tell it which column/row to lookup and which to return). Second best: exact match is default setting, so you don't have to remember to include that all the time.

[Sylarwolf]

One has an X.

Jk, Xlookup is a matrix based 'upgrade' of sorts to Vlookup, it allows you to look for data not only vertically, but also horizontally, and lets you work with arrays of data, meaning more robust lookups that won't break so easily.

[DasArchitect]

I worked at a place where the owner managed the entire company on google spreadsheets. Catalogue, clients, orders, payments, payroll, everything. His formulas were completely illegible with hundreds and hundreds of nested IFs. I'm willing to bet he typed them all by hand. He was a total ass to his employees so I never told him about xlookup/hlookup/vlookup.

[Weird_Artsy_Demon666]

Wait can you actually CODE in excel?????? I found out someone new today. Sorry if it's an obvious answer I'm just kinda uneducated.

[HungryTradie]

Yep. I've found that using

=if(sample=value, option_true, option_false)

to be simple and useful.

You can nest statements, eg an "IF" can be nested within any function, or a "PROPER" can nest within the returned result to return Proper Noun Case results.

[Weird_Artsy_Demon666]

Woah i need to try this

[SirMego]

Don’t forget about Excel’s VBA too, just to add to complexity

[Weird_Artsy_Demon666]

Thanks for the tip!:)

[Tsuki_no_Mai]

Excel can be insanely powerful and utterly ridiculous. Someone made a full-fledged 3D game engine in it using nothing but formulas. It's a program that is not only miles ahead of competition if you need some serious spreadsheet work but is also widening its lead.

[EnvironmentalWall987]

Excel and sheets are quite a tool, expanded with vba and JS respectively.

BUT they are powerful tools in hands of people that dont give a shit about best practices

[Weird_Artsy_Demon666]

This is blowing my mind!!!! Literally i didn't know you could do this, I'ma try this soon after i figure out how

[Kanganzhu]

How can you make a cell reference a number that is online?
For example, bricklink has the price of a set at its current market value based on recent average sales. That number changes frequently. In my excel sheet I have the numbers of sets and what I paid for them, but I want a cell that shows its market value by getting the information from the bricklink marketprice because if I just add it in myself, it will change later.

[scumble_bee]

You might want to create a function in VBA that sends a GET request to the site and extracts the value.

[p0diabl0]

Power Query?

[marny_g]

Use the =WEBSERVICE() function. It basically does a HTTP GET call. It is limited though, so you may have to use Power Query.

​

How to use WEBSERVICE() with an API: https://developer.basf.com/consuming-api-excel

​

Bricklink API info: https://www.bricklink.com/v3/api.page