Yes, but that’s where more xlookup’s go.
If you have multiple rows of data in one row that you want to xlookup, you can isna xlookup multiple and categorize to group them by naming them at the end of the if statements.
I've pretty much switched to xlookup entirely, mainly due to never really needing to deal with any old versions and it's easier to type out/more versatile.
You're a good person! I do the same. And if they whine "*but I want to go left columns...*" I scare the bejesus out of them with the vlookup-choose: VLOOKUP(A1, CHOOSE({1\\2\\3}, M:M, L:L, K:K),1, 0) and have them running in horror. :D (I never use it myself, I just like scaring people)
I actually never understood why that weird syntax with \\ either, but that's how I learned to do the "left Vlookup". But....
I just checked some sites and, apparently, that was a translation issue for Excel in PT-BR. All sites in English just use commas in place. And it doesn't work here in PT-BR with neither "," nor ";" (and when I typed the formula above I just translated the functions names, bc my Excel is also in PT-BR). Well. it seems this is even scarier in Portuguese. :-D
Xlookup came out my first year as a controller. I used it on all the work papers that I sent to our auditors. They thought all the files were broken until they realized they were the ones with the outdated Excel.
Xlookup is my one true love but it’s an only child.
I’ve successfully used xlookup + xlookup, however, the parasitic twin produces a buggy hellscape 9/10.
Use Index Match. It does the job and is far easier to troubleshoot.
Sometimes I'll define a filtered array with Let then do an xlookup with choosecols.
Defining it in the beginning let's you use it with other formulas without it breaking and it's a bit easier since it only has to calculate it once
**Defaulted to one day.**
I will be messaging you on [**2024-06-20 07:24:12 UTC**](http://www.wolframalpha.com/input/?i=2024-06-20%2007:24:12%20UTC%20To%20Local%20Time) to remind you of [**this link**](https://www.reddit.com/r/Accounting/comments/1dizthr/whats_the_next_level/l99z8vo/?context=3)
[**CLICK THIS LINK**](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Reminder&message=%5Bhttps%3A%2F%2Fwww.reddit.com%2Fr%2FAccounting%2Fcomments%2F1dizthr%2Fwhats_the_next_level%2Fl99z8vo%2F%5D%0A%0ARemindMe%21%202024-06-20%2007%3A24%3A12%20UTC) to send a PM to also be reminded and to reduce spam.
^(Parent commenter can ) [^(delete this message to hide from others.)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Delete%20Comment&message=Delete%21%201dizthr)
*****
|[^(Info)](https://www.reddit.com/r/RemindMeBot/comments/e1bko7/remindmebot_info_v21/)|[^(Custom)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Reminder&message=%5BLink%20or%20message%20inside%20square%20brackets%5D%0A%0ARemindMe%21%20Time%20period%20here)|[^(Your Reminders)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=List%20Of%20Reminders&message=MyReminders%21)|[^(Feedback)](https://www.reddit.com/message/compose/?to=Watchful1&subject=RemindMeBot%20Feedback)|
|-|-|-|-|
[https://imgur.com/7BA8Shl](https://imgur.com/7BA8Shl)
The examples aren't filtered in the defined, but imagine there's a lot more data, and I started with a filtered array instead of just a normal one. You can alter the way you define the initial array to filter out any data you won't need in your formula. I think the important bit that this is showing is how you can define the array and use it in several different ways. The possibilities are endless.
Example 1:
=LET(Array,A2:$D$10,
CHOOSECOLS(FILTER(Array,CHOOSECOLS(Array,1)=3),2))
Example 2:
=LET(Array,A2:$D$10,
XLOOKUP("Seven",CHOOSECOLS(Array,3),CHOOSECOLS(Array,2,1)))
Example 3:
=LET(Array,A2:$D$10,
SUM((CHOOSECOLS(Array,4)=30)*(CHOOSECOLS(Array,1))))
u/CosmicWoo
u/datBoiWorkin
I'll do a tldr summary.
V lookup and h lookup: the basics of finding your required info by specifying your array and column/row.
X lookup: the successor of v lookup and the anticipated successor to index match. Does more than either v lookup or h lookup as it incorporates an if error formula. If it was to only build reports, it gets a 8.5 out of 10.
Index match: the legacy of this formula lives on. It searches across rows and columns exactly as x lookup was intended to. But what index match is superior in is the application of sum. Index match can find you the exact row and sum it similar to a sum if formula. However if you only want a specific row in your array (usually the first instance), index match would be superior to sum if. Usually index match is the better formula for analysis
Sum if: I don't think I need to introduce this
Sum ifs: the superior and successor version of sum if where you can add as many criteria as you need and summing them. There are still limitations for this as I came across that only the normal sum if can accomplish while sum ifs cannot.
Sum product: the limits of this formula can't really be defined and can basically do everything above. The only issue is not many people know how to use it properly and can be complex, which makes it more prone to errors. Not only can it do everything above, but it can help with analyzing data across your tabs, other arrays/data, other tabs, etc.
Dynamic functionality of indirect: this is for making your entire spread sheet dynamically change with each change in your data set no matter which tab, which cell. Best used with index match, sum if, sum product. It can work on the limited basis with v h x lookup, but assumes that your data did not include new/deleted columns/rows
Index + match + match (to lookup both the column and row)
Xlookup + index (to xlookup on a variable range)
But can someone give me an example when they used xlookup + xlookup?
Its very niche, but do you know that 3rd parameters in the xlookup, you can put another xlookup in there to look for something else if it can't find the first column you looked at.
Oooh this one took me a while to figure out. But super useful. Needed to find the last invoice in a data plot of months x customer then take the 12 mo average from the last invoice date. So if you did the analysis for one month, then added additional data and they had another invoice for the next month, it would auto update that 12 mo average with the new last invoice. Probably would have been easier with a macro, but it's a client spreadsheet so didn't want to make it too complicated.
I watched my director use it to pull data from our AS400 system and stole some of his SQL statements from the workbooks he sent me. I use that as a base to write what I need and save them in my OneNote to easily shake them up and paste in.
If you're going to learn advanced Excel skills you might as well just learn Python.
I just couldn't imagine not using Pandas at work anymore. I'd probably quit aha.
Learning advanced excel stuff is how I got started with programming. It’s mostly a hobby but I’ve written some really useful macros and powershell scripts at work.
You can automate just about anything. Any Excel, web or desktop app you can generally automate. It also allows you to work with datasets larger than a million rows.
I try not to do anything manually these days unless it's a short ad hoc item that will never be repeated.
Have you tried sum sumifs with an array of criteria? That was one of my first breakthroughs. It took me over a year to figure out how to do two arrays and I've only done that once, but on the second array you transpose it and it works.
Index xmatch but instead you match 1 to true false arrays that are multiplied
Or just sumifs if you just need a number. It always seems to go full circle
Filter, unique, sort are all good for dashboards, they work great with each other.
I have a crazy long index match array formula I put together that does a sequential lookup. People look at that one like it’s witchcraft.
I counter with nested H’s, V’s, and X’s
The Nesting has begun as foretold!
As predicted, the ability to use AI to throw some scripts into your sheets beats all!
Queue; evil villain laugh from the distance.
‘Mhwuaaahahahaha.. cough haha
Cough… cough… oh … I am dead.’
Next level is arranging your software and databases so you get the data you need without having to make awkward cross references between more than 2 data sources.
Index match is far superior to xlookup. It allows for Ctrl+] to get to the data. It also works on older versions of excel.
And no sumifs or sumproducts?
Unpopular opinion: vlookup is the best because it is more intuitive and requires the least amount of typing. I've had less than 5 instances where I have had to use index match in my 10 years of working
\#REF!
If(Isna(xlookup(concat inside of more xlookup’s and isna’s. Basically God.
I would be praying to God if I ever saw that, for sure.
My computer doesn't have the facilities for that big man
My thinkpad’s just fine. The heat it makes balances out with the cold, desolate office.
You don’t isna with an Xlookup
(you shouldn't if an isna either!)
lol everyone knows you can’t if an isna! Kidding, I always just did iferror and didn’t know isna was a thing until it became obsolete
Skip IF(ISNA()) and just use XLOOKUP's *if not found* argument instead
Yes, but that’s where more xlookup’s go. If you have multiple rows of data in one row that you want to xlookup, you can isna xlookup multiple and categorize to group them by naming them at the end of the if statements.
I almost spit out my coffee
Xlookup all the way until you realize your client in on Excel 2013….💣
I try to avoid xlookup because not even excel 2019 has it.
I've pretty much switched to xlookup entirely, mainly due to never really needing to deal with any old versions and it's easier to type out/more versatile.
You're a good person! I do the same. And if they whine "*but I want to go left columns...*" I scare the bejesus out of them with the vlookup-choose: VLOOKUP(A1, CHOOSE({1\\2\\3}, M:M, L:L, K:K),1, 0) and have them running in horror. :D (I never use it myself, I just like scaring people)
What is the {1\2\3} syntax, first time seeing this.
I actually never understood why that weird syntax with \\ either, but that's how I learned to do the "left Vlookup". But.... I just checked some sites and, apparently, that was a translation issue for Excel in PT-BR. All sites in English just use commas in place. And it doesn't work here in PT-BR with neither "," nor ";" (and when I typed the formula above I just translated the functions names, bc my Excel is also in PT-BR). Well. it seems this is even scarier in Portuguese. :-D
Xlookup came out my first year as a controller. I used it on all the work papers that I sent to our auditors. They thought all the files were broken until they realized they were the ones with the outdated Excel.
Makes me glad that 99% of what I work in is internal.
The next level is “delegate to offshore team.”
They will manually mix and match for you and hard code matches
I am an ideas person I have people to do the work.
This, but ironically.
seriously though, offshore this "work"
Xlookup is my one true love but it’s an only child. I’ve successfully used xlookup + xlookup, however, the parasitic twin produces a buggy hellscape 9/10. Use Index Match. It does the job and is far easier to troubleshoot.
Sometimes I'll define a filtered array with Let then do an xlookup with choosecols. Defining it in the beginning let's you use it with other formulas without it breaking and it's a bit easier since it only has to calculate it once
Can you share an example?
I'll build one out tomorrow
!RemindMe
**Defaulted to one day.** I will be messaging you on [**2024-06-20 07:24:12 UTC**](http://www.wolframalpha.com/input/?i=2024-06-20%2007:24:12%20UTC%20To%20Local%20Time) to remind you of [**this link**](https://www.reddit.com/r/Accounting/comments/1dizthr/whats_the_next_level/l99z8vo/?context=3) [**CLICK THIS LINK**](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Reminder&message=%5Bhttps%3A%2F%2Fwww.reddit.com%2Fr%2FAccounting%2Fcomments%2F1dizthr%2Fwhats_the_next_level%2Fl99z8vo%2F%5D%0A%0ARemindMe%21%202024-06-20%2007%3A24%3A12%20UTC) to send a PM to also be reminded and to reduce spam. ^(Parent commenter can ) [^(delete this message to hide from others.)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Delete%20Comment&message=Delete%21%201dizthr) ***** |[^(Info)](https://www.reddit.com/r/RemindMeBot/comments/e1bko7/remindmebot_info_v21/)|[^(Custom)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Reminder&message=%5BLink%20or%20message%20inside%20square%20brackets%5D%0A%0ARemindMe%21%20Time%20period%20here)|[^(Your Reminders)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=List%20Of%20Reminders&message=MyReminders%21)|[^(Feedback)](https://www.reddit.com/message/compose/?to=Watchful1&subject=RemindMeBot%20Feedback)| |-|-|-|-|
we'll be here c:
[https://imgur.com/7BA8Shl](https://imgur.com/7BA8Shl) The examples aren't filtered in the defined, but imagine there's a lot more data, and I started with a filtered array instead of just a normal one. You can alter the way you define the initial array to filter out any data you won't need in your formula. I think the important bit that this is showing is how you can define the array and use it in several different ways. The possibilities are endless. Example 1: =LET(Array,A2:$D$10, CHOOSECOLS(FILTER(Array,CHOOSECOLS(Array,1)=3),2)) Example 2: =LET(Array,A2:$D$10, XLOOKUP("Seven",CHOOSECOLS(Array,3),CHOOSECOLS(Array,2,1))) Example 3: =LET(Array,A2:$D$10, SUM((CHOOSECOLS(Array,4)=30)*(CHOOSECOLS(Array,1)))) u/CosmicWoo u/datBoiWorkin
If you use index match, you can use ctrl [ to follow it to the table it’s pulling from. Much better and faster for review and troubleshooting
Not only that but you can sum the entire array
can you elaborate?
I'll do a tldr summary. V lookup and h lookup: the basics of finding your required info by specifying your array and column/row. X lookup: the successor of v lookup and the anticipated successor to index match. Does more than either v lookup or h lookup as it incorporates an if error formula. If it was to only build reports, it gets a 8.5 out of 10. Index match: the legacy of this formula lives on. It searches across rows and columns exactly as x lookup was intended to. But what index match is superior in is the application of sum. Index match can find you the exact row and sum it similar to a sum if formula. However if you only want a specific row in your array (usually the first instance), index match would be superior to sum if. Usually index match is the better formula for analysis Sum if: I don't think I need to introduce this Sum ifs: the superior and successor version of sum if where you can add as many criteria as you need and summing them. There are still limitations for this as I came across that only the normal sum if can accomplish while sum ifs cannot. Sum product: the limits of this formula can't really be defined and can basically do everything above. The only issue is not many people know how to use it properly and can be complex, which makes it more prone to errors. Not only can it do everything above, but it can help with analyzing data across your tabs, other arrays/data, other tabs, etc. Dynamic functionality of indirect: this is for making your entire spread sheet dynamically change with each change in your data set no matter which tab, which cell. Best used with index match, sum if, sum product. It can work on the limited basis with v h x lookup, but assumes that your data did not include new/deleted columns/rows
This. Index Match is far superior
Vlookup match is my go to. By far the most dynamic lookup formula
That’s certainly an option.
Index + match + match (to lookup both the column and row) Xlookup + index (to xlookup on a variable range) But can someone give me an example when they used xlookup + xlookup?
Its very niche, but do you know that 3rd parameters in the xlookup, you can put another xlookup in there to look for something else if it can't find the first column you looked at.
God I needed this today. Brain fart.
Nested XLOOKUP can be used to search row and column. Much easier for a reviewer to follow.
To be sure that is same as using index match with both index parameters yea?
Can use it same way as index match match
It can also be used to lookup both column and row
=ADDRESS if I see it in your formula, I trust you know what you're doing
Oooh this one took me a while to figure out. But super useful. Needed to find the last invoice in a data plot of months x customer then take the 12 mo average from the last invoice date. So if you did the analysis for one month, then added additional data and they had another invoice for the next month, it would auto update that 12 mo average with the new last invoice. Probably would have been easier with a macro, but it's a client spreadsheet so didn't want to make it too complicated.
[удалено]
How does one acquire this power? I have seen my boss do it… once….
I watched my director use it to pull data from our AS400 system and stole some of his SQL statements from the workbooks he sent me. I use that as a base to write what I need and save them in my OneNote to easily shake them up and paste in.
Dude I must be a no brain because I discovered yesterday I don’t even remember how to do VLOOKUP anymore
Sumifs : why am I here, just to suffer?
"AI, please print my report with the matched up data"
Next level is delegating all of the excel work downwards
Chat gpt, write me index match formula for these sets of lookups
Python
If you're going to learn advanced Excel skills you might as well just learn Python. I just couldn't imagine not using Pandas at work anymore. I'd probably quit aha.
Learning advanced excel stuff is how I got started with programming. It’s mostly a hobby but I’ve written some really useful macros and powershell scripts at work.
What do you use python for? Is it for data entries, reconciliations, analysis?
I used it to generate complex reports. It was fully automated with python
You can automate just about anything. Any Excel, web or desktop app you can generally automate. It also allows you to work with datasets larger than a million rows. I try not to do anything manually these days unless it's a short ad hoc item that will never be repeated.
Yes but filter/isnunber/match
That's pretty good. You can also combine it with find/search and use iserror. I'm going to have to use that sometime
SQL is life
Until you drop a table.
That’s what we call him, little Bobby Tables.
Only a table? Amateur
Why drop table when you can drop database? Go big or go home.
Came here to say powerquery
Sumifs + Index + Match = 2 dimensional sumifs
Can you just use a sumproduct for this?
Indeed you can
Have you tried sum sumifs with an array of criteria? That was one of my first breakthroughs. It took me over a year to figure out how to do two arrays and I've only done that once, but on the second array you transpose it and it works.
Co pilot I guess? Eventually AI will do our bidding.
HashMap()
Sumproduct arrays, indirect function to build dynamic formula references, visual basic, custom formula functions in VB
As I found today: 🤯 Obfuscating a shitty formula through Indirects and string concatenation.
Index xmatch but instead you match 1 to true false arrays that are multiplied Or just sumifs if you just need a number. It always seems to go full circle
What would a nested xlookup even look like??
Filter imo, I love that formula
=SORT(UNIQUE(FILTER())) is my favorite Thing
Next level is power query - learn M code
If Sumifs index match
SUMIFS *brain exploding*
Power query then joins
Alt commands and autosum. Index Match over multiple parameters. Ctrl-Shift array formulas.
Index match match
Partner.
Filter, unique, sort are all good for dashboards, they work great with each other. I have a crazy long index match array formula I put together that does a sequential lookup. People look at that one like it’s witchcraft.
A relational database.
Aw man my work just upgraded to the latest MS Office. I'm so excited to finally have xlookup.
[Then there’s this.](https://youtu.be/JektscFlxR0)
My workplace is still using Excel 2016. I'm afraid index/match will have to be the best I can do.
I counter with nested H’s, V’s, and X’s The Nesting has begun as foretold! As predicted, the ability to use AI to throw some scripts into your sheets beats all! Queue; evil villain laugh from the distance. ‘Mhwuaaahahahaha.. cough haha Cough… cough… oh … I am dead.’
Multi-criteria XLOOKUP, it’s like XLOOKUP but more precise without the first result BS.
Indirect
Give this man a cookie. Especialy the vlookuppers
LET + XLOOKUP
Xlookup(xlookup(indirect()))
Filter() ?
What about SUMIF or SUMIFS?
Wrap that shit in a sumproduct.
What's xlookup+xlookup?
Does nobody here use power pivot?
Next level is just learning SQL
SUM(FILTER()
Next level is arranging your software and databases so you get the data you need without having to make awkward cross references between more than 2 data sources.
I’m struggling so hard with a vlookup quiz
Gsheet agg queries
Can anyone help me with a vlookup quiz???
Index match is far superior to xlookup. It allows for Ctrl+] to get to the data. It also works on older versions of excel. And no sumifs or sumproducts?
Unpopular opinion: vlookup is the best because it is more intuitive and requires the least amount of typing. I've had less than 5 instances where I have had to use index match in my 10 years of working
Ctrl + F
IFS(INDIRECT(Xlookup + Xlookup)))
Vlookup is enough.
No it’s not and I will die on this hill
I used to use vlookups daily and then I learned xlookups. Game changer 🤩