Recently discovered how Lambda functions work, and this is a great example, did not know that copying and pasting the formula would carry the formula over, great tip!
Hello Mark, thank you for the inspiration to think further factory functions. Also mentioning Sandor Debreceni due to our meaningful conversations. My version 1) needs no table format source, 2) only takes one argument, which is the full source range and 3) shows missing lookup values with two different error handling icons (missing row lookup 🔙 and missing column lookup 🔝). For adding as own formula, paste this: =LAMBDA(fullsource;LET(r;ROW();c;COLUMN(); FirstRow;ADDRESS(r;c-1;4;1); FirstCol;ADDRESS(r-1;c;4;1); LastRow;XMATCH(FALSE;ISTEXT(INDIRECT(FirstRow&":"&ADDRESS(999999;c-1;4;1)));0)-1; LastCol;XMATCH(FALSE;ISTEXT(INDIRECT(FirstCol&":"&ADDRESS(r-1;15999;4;1)));0)-1; VerticalHeader;INDIRECT(FirstRow&":"&ADDRESS(r+LastRow-1;c-1;4;1)); HorizontHeader;INDIRECT(FirstCol&":"&ADDRESS(r-1;c+LastCol-1;4;1)); MatchRows;IFERROR(XMATCH(VerticalHeader;CHOOSECOLS(fullsource;1);0);1/0); MatchCols;XMATCH(HorizontHeader;CHOOSEROWS(fullsource;1);0); MAP(INDEX(fullsource;MatchRows;MatchCols);LAMBDA(x;IFERROR(SWITCH(ERROR.TYPE(x);2;"🔙";7;"🔝");x)))))
Mark, you are an angel, you have helped me so much in my professional life and thanks to you I have progressed in the workplace. God bless you, thank you always for all the help, amazing man and so much fun that you share your genius with us!
@ExcelOffTheGrid 3D and more exists - I saw it and I use it. 😊 I found one way: inside LET I create 2D (1st array to be looked up makes column headers, and the 2nd one - row ones), then I concatenate them, and then I TOCOL the combined texts, so now I've got 1 array instead of 2. Then I repeat it to add 3rd dimension etc. Finally I transpose the combined texts, so again it's kind of 2D: the "looked up" are the column headers - and the "looked in" go down. Then I do the actual lookup. And then I shape it. 3D is actually n 2D squares next to each other, on the same or on the following sheets. For 3D, VSTACK works well, with an additional 1st column that shows which dimension we have in a given row. 4D goes both down and right. 5D goes to the next sections on the same sheet, in one direction (VSTACKed blank rows/columns can visually separate it). 6D to the next sections in both directions. 7D goes to the next supersection, etc. At any point you can go to next sheets. Or you can stay with a column of results, and a column of combined texts that came from different dimensions. (This way I dynamically search by the case weight (I want always the highest possible) and case categories (like week number, channel, status etc. etc. - where in a month I want all of them covered at least once, if possible). With every data update I keep the previous lookup results and I try to skip what was already covered.) (To skip what I don't need, I make it an error, and then I use argument 2 in TOCOL.) It's big but it's just a loop of folding and hammering. I mean, it can inspire you a little. Or not. 😊
This is amazing! Huge potentials available with this formula I was looking at an easy way to make custom formulas available across all workbooks. I found this very old suggestion (2002), do you think it would still work now? Save the file with the function with an xla extension. This will tell Excel that it's an add-in workbook. Then Select Tools > Add-Ins from the menu and browse to the xla file you saved. The function will now be available to all workbooks in Excel.
... once again, well explained. You should work as a motivational trainer for LAMBDA and LET. The way you explain should get even the last one out of the couch and into the learning mode.
Dear Mark. Its SUPERXLOOKUP function is very good, but it needs to be named in the workbook. With the INDEX and XMATCH functions, existing in any Excel, you can obtain the same results: =IFERROR(INDEX(Data,XMATCH(H6:H9,Data[Item]),XMATCH(I5:L5,Data[#Headers])),0) =IFERROR(INDEX(Data,XMATCH(H13:H20,Data[Item]),XMATCH(I12:K12,Data[#Headers])),0) 🤗
INDEX/MATCH/MATCH is a 2-way lookup. The solution in the video is a 1-way lookup which spills into 2 dimension. So they are different and will serve different use cases.
@@ExcelOffTheGrid I was referring to the difficulty of always having the SUPERXLOOKUP function available, while the INDEX and XMATCH functions are always available and you get the same spilled results. 🤗
Hats off Mark that is pure awesomeness. The LET was fantastic but then the LAMBDA, too good. But what about poor FILTER function, is your custom formula walking its side of the street? I am wondering what are the pros and cons to a FILTER based formula alternative. ISOMITTED as well when did that come in, nice.
FILTER is based on data order and not the order of the values you've requested, it also includes all values (not just the first / or last), it does not return anything for values that don't exist and it also doesn't fully support wildcard characters. So, it could be partially possible with FILTER but would be significantly more complex and only part of the solution.
Mark -- in the Lambda version, 2nd example (with headers of Q1, Q2, Q5, it is an error if the items contains an invalid one, like ZZZZ instead of Charlie. It seems to ignore the categories and instead assumes all is OK! As a matter of fact, if you completely clear the items, the array returned doesn't change!
The function is solving the array of arrays problem. It performs a 1 way lookup and returns a 2-day array. So, yes it is working as it’s meant to. The additional values are only to help people understand the values returned by the function.
Mark - Love your videos - they are an incredible learning tool. Recently got laid off and thinking of starting up a YT channel (at over 50 it is daunting). I love your video setup/quality. Could you possibly share your equipment/editing/setup? Your lighting is great, and I really like the warm lighting the lamp in the background offers. Sorry for the non-Excel question!
Can you go to exceloffthegrid.com/customer-support Put your email address in there and then we can continue the conversation on email. As UA-cam doesn’t provide the best format for longer text. 👍
Thanks a bunch. This will help me a ton. with this method, I hope you could also help replicate the PIVOTBY and GROUPBY functions as they are still not available to old versions of excel.
I'm glad you can use it. In terms of alternatives to GROUPBY/PIVOTBY there are some simple scenarios which can be replicated in other ways. But those formulas are so complex that I wouldn't even try.
Hats off to Mark, excellent formula that i use it to lot extent. I request you to enhance with one more way where It lookups h2:h9 in a:a and returns value based on column headder line in hotizantal (q1, q3, q5) lookup. Hope you get this.
Great work, I have one question though. How would avoid the user error? Your assumption is that the list in H12 : H20 is in the exact the same order as in the table. What if a user changes this say the order, then the entire data is not displayed in an orderly manner and gives wrong results. Could on include also the item column to be on the safe side.
Use a HSTACK in the return_array to select specific columns. Or use a formula which gives you a 2-way lookup. Though, if column order could change it sounds like you’ve got a data issue rather than a formula issue.
Nice. But is it better than something like this, which can be converted into a nice Lambda function: IFERROR(INDEX(range),XMATCH(),XMATCH()),if_not found) This doesn't assume you want all columns if you're looking up rows and vice versa.
It’s a slightly different use case. If you want a 2-way lookup, then INDEX/XMATCH/XMATCH is better. But, if you want a 1-way lookup that spills in both directions, then I still stand by my solution.
@@ExcelOffTheGrid As an engineer, most of the lookups I've ever had to do require a 2-way lookup; that's why I never even learned VLOOKUP. I get that you've solved the 2-way spill for a 1-way lookup. I got bogged down trying to use BYROW and REDUCE, and it got ugly. But if you have both sets of headers anyway (items and quarters), the 2-way lookup gives the same results.
Really good but the 8th letter of thealphabet appears under A in the dictionary because its name is Aitch and does noy have an aspirated start. Aitch is the name of H.
FILTER has other quirks which make it great from some scenarios but not others. FILTER is based on data order and not the order of the values you've requested, it also includes all values (not just the first / or last), it does not return anything for values that don't exist and it also doesn't fully support wildcard characters. So, would be a challenge to make it work in this scenario.
Super-great video that is action-packed and up-to-speed. Great stuff, Mark! For those for whom this went too fast: you can always play back the video on half speed… ;-) FYI: I would’ve added a conceptual line where you explain “why” this works, avoiding the array-within-an-array conundrum. But maybe I just missed it in the fast-paced action of the video. ;-)
I partly covered that in the last video, so didn't want to go back over that. But definitely anybody who didn't see that might wonder what it was all about.
Because a 1-way lookup spilling across two dimensions is different to a 2-way lookup spilling across two dimensions. A 2-way lookup is scalar lifting in both directions, while the one-way lookup is scalar lifting in one direction and an array in the other. So, the use cases are different.
I think the Index function is volatile which could lead to poor performance. I have been using drop(Reduce("", keys, lambda(key, acc, vstack(acc, xloolup(key, in,return)))) ,1) which i think is probably slower but so l at least not volatile Edit: Index isn't volatile I was thinking of offset
@@ExcelOffTheGrid I was getting mixed up with offset! I'll leave the comment since it's a useful alternative technique to this sort of problem (e.g. works with jagged arrays in 2d filter) but I like your formula more for this scenario
But then it doesn’t return multiple lookup values at the same time - it triggers the array of arrays problem. This solution solves that . That is the point.
@@ExcelOffTheGrid maybe I am misunderstanding you, maybe my English is a little challenged when it comes to Excel (I am from Denmark). I can make a lookup in excel when searching for a particular name, and it can return his salary, address and other columns within a normal XLOOKUP. But this is not what you mean? In the beginning of the video, you show that XLOOKUP only returns one of these values, but mine can return several. I am not trying to criticize your video, I just do not get the same result.
As an old person who still uses VLOOKUP, is the appeal of writing so many functions that you don’t want to use absolute references and just copy the formulas down and right? Like, this stuff is certainly neat, but it’s also a lot more work. I’m sure I’m missing something here.
Yes, the maintenance element. When data increases/decreases you have to keep copying your ranges down/right. With dynamic arrays, you don't have to do anything; it all updates itself.
@GusCaravalho I'd say: the size of the file. If you have a formula (100 characters long) for 100 rows, if it is a 1-cell formula, the total weight is 100×100 characters - while for a spill formula it is 1×100 characters (because it actually sits only in the 1st cell. Some my files shrinked by megabytes, and as a consequence they also open faster and refresh faster. Actually the old-good VLOOKUP spills very well too (if you search many-in-many) and the numeric parameter (for column) is very handy in many cases. And old functions are often faster than the newest - the more rows you have the more you appreciate it. But I mostly use XLOOKUP. 😊
We’re not building a 2 way lookup. It’s one lookup that spills multiple columns. The example might look like a 2-way lookup, but that is just to illustrate horizontal & vertical calculation.
@ExcelOffTheGrid I did actually learn more about using lambda than super lookup so it wasn't a total waste lol. Helped me save a bit of time working out employers NI costs
Pls get me a Formula to count meals (breakfasts, lunches & Diners) between 2 dates in different columns i.e 20/11/2024 Diner to 24/11/24 Breakfast BF (Column) 4nos Lunch (Column) 3nos Diner (Column) 4nos If you give me a solution i will be very greatful.
These formulas may be a better alternative - CHOOSECOLS(Table23,XMATCH(J1:M1,Table23[#Headers],0,1)) CHOOSEROWS(Table23,XMATCH(P11:P19,Table23[SalesMan],0,1))
What a creative name - SUPERXLOOKUP and great functionality! I think Microsoft should buy this function from you!
Maybe one day they will give is an array-of-arrays compatible version.
Tons of talent, Mark. Excellent resolution, which also controls errors. Awesome. Thanks for sharing.
My pleasure! I'm glad you liked it Ivan. 😁
Just sent the link to this video to my work email. Will be trying this out tomorrow. Great work, exceptional!
Great stuff... give it a go. 👍
Recently discovered how Lambda functions work, and this is a great example, did not know that copying and pasting the formula would carry the formula over, great tip!
Copy/Paste is the easiest way I've found without using anything advanced. You can also copy and sheet, and it will move all your LAMBDAs at once.
Incredible!
I love videos that use formulas.
Glad you like them!
Hello Mark, thank you for the inspiration to think further factory functions. Also mentioning Sandor Debreceni due to our meaningful conversations. My version 1) needs no table format source, 2) only takes one argument, which is the full source range and 3) shows missing lookup values with two different error handling icons (missing row lookup 🔙 and missing column lookup 🔝). For adding as own formula, paste this: =LAMBDA(fullsource;LET(r;ROW();c;COLUMN(); FirstRow;ADDRESS(r;c-1;4;1); FirstCol;ADDRESS(r-1;c;4;1); LastRow;XMATCH(FALSE;ISTEXT(INDIRECT(FirstRow&":"&ADDRESS(999999;c-1;4;1)));0)-1; LastCol;XMATCH(FALSE;ISTEXT(INDIRECT(FirstCol&":"&ADDRESS(r-1;15999;4;1)));0)-1; VerticalHeader;INDIRECT(FirstRow&":"&ADDRESS(r+LastRow-1;c-1;4;1)); HorizontHeader;INDIRECT(FirstCol&":"&ADDRESS(r-1;c+LastCol-1;4;1)); MatchRows;IFERROR(XMATCH(VerticalHeader;CHOOSECOLS(fullsource;1);0);1/0); MatchCols;XMATCH(HorizontHeader;CHOOSEROWS(fullsource;1);0); MAP(INDEX(fullsource;MatchRows;MatchCols);LAMBDA(x;IFERROR(SWITCH(ERROR.TYPE(x);2;"🔙";7;"🔝");x)))))
I have immediate use for this function. Thanks a bunch!
Awesome, I'm glad you can put it to good use.
Mark, you are an angel, you have helped me so much in my professional life and thanks to you I have progressed in the workplace.
God bless you, thank you always for all the help, amazing man and so much fun that you share your genius with us!
Thank you for those kind words. I really appreciate that. 😁
This really is off the grid 💪🏼
As usual, you are always EXCELLing yourself
Thanks, I appreciate that. 😁
Thank you so much for this. I have this urge to learn more about Excel!
Can't wait for the SuperDuperXLOOKUP video coming out where Mark builds a formula that spills not only on the X and Y axis, but also the Z axis! 😁
That would be amazing to spill 3 dimensionally... we can dream.
@ExcelOffTheGrid 3D and more exists - I saw it and I use it. 😊
I found one way: inside LET I create 2D (1st array to be looked up makes column headers, and the 2nd one - row ones), then I concatenate them, and then I TOCOL the combined texts, so now I've got 1 array instead of 2. Then I repeat it to add 3rd dimension etc. Finally I transpose the combined texts, so again it's kind of 2D: the "looked up" are the column headers - and the "looked in" go down. Then I do the actual lookup.
And then I shape it. 3D is actually n 2D squares next to each other, on the same or on the following sheets. For 3D, VSTACK works well, with an additional 1st column that shows which dimension we have in a given row. 4D goes both down and right. 5D goes to the next sections on the same sheet, in one direction (VSTACKed blank rows/columns can visually separate it). 6D to the next sections in both directions. 7D goes to the next supersection, etc. At any point you can go to next sheets.
Or you can stay with a column of results, and a column of combined texts that came from different dimensions.
(This way I dynamically search by the case weight (I want always the highest possible) and case categories (like week number, channel, status etc. etc. - where in a month I want all of them covered at least once, if possible). With every data update I keep the previous lookup results and I try to skip what was already covered.)
(To skip what I don't need, I make it an error, and then I use argument 2 in TOCOL.)
It's big but it's just a loop of folding and hammering. I mean, it can inspire you a little. Or not. 😊
Can't wait for your next formula. Superb! Thanks a lot.
I might call it NEXTLEVELSUPERXLOOKUP()... or maybe that's a step to far 😂
incredible.. very impressive, thank you for your work
Thanks, I'm glad you liked it.
So EXCELlent presentation....It contains everything (XLOOKUP function evaluation from excel team 😉)
Thanks, I’m glad you liked it.
great job, sir, thanks for such an excellent formula.
You are most welcome - I'm glad you liked it.
Mark, u are not human!
👏👏👏👏👏
Thanks 😁
Outstanding Mark, what a great solution. Thank you
Thanks, I'm glad you like it.
Nice one Mark- this really would be a great built in function.
Maybe one day Microsoft will add something similar.
Mark - this is superb - thank you.
You’re welcome - I’m glad you liked it.
You are really a genious..
Mark,
This is awesome ❤
In your next video, please do it using Power Query.
Thanks in advance 😅
This is amazing! Huge potentials available with this formula
I was looking at an easy way to make custom formulas available across all workbooks.
I found this very old suggestion (2002), do you think it would still work now?
Save the file with the function with an xla extension. This will tell Excel that it's an add-in workbook. Then Select Tools > Add-Ins from the menu and browse to the xla file you saved. The function will now be available to all workbooks in Excel.
No, that won’t work that only applies VBA functions.
However, you could do this:
ua-cam.com/video/wNp9A31wXvQ/v-deo.html
@@ExcelOffTheGridlove it! Thank you!
... once again, well explained. You should work as a motivational trainer for LAMBDA and LET. The way you explain should get even the last one out of the couch and into the learning mode.
I'm still learning all this stuff myself, and it's certainly a lot of fun.
@ExcelOffTheGrid ... we can do this together from the next week 😉 ... I sign up for a membership 🏁 ... my way to support you
Brilliant Mark, Thank you so much 🎉
My pleasure! 😁
Dear Mark.
Its SUPERXLOOKUP function is very good, but it needs to be named in the workbook.
With the INDEX and XMATCH functions, existing in any Excel, you can obtain the same results:
=IFERROR(INDEX(Data,XMATCH(H6:H9,Data[Item]),XMATCH(I5:L5,Data[#Headers])),0)
=IFERROR(INDEX(Data,XMATCH(H13:H20,Data[Item]),XMATCH(I12:K12,Data[#Headers])),0) 🤗
INDEX/MATCH/MATCH is a 2-way lookup.
The solution in the video is a 1-way lookup which spills into 2 dimension.
So they are different and will serve different use cases.
@@ExcelOffTheGrid I was referring to the difficulty of always having the SUPERXLOOKUP function available, while the INDEX and XMATCH functions are always available and you get the same spilled results. 🤗
this is awesome thanks for sharing Mark 😊☘
My pleasure - I’m glad you liked it.
Brilliant, just brilliant!
Thank you.
Gor-blimey, this is a bit good! Many thanks, Mark.
Thanks Ian. Loads of advanced techniques in there to keep you happy.
Mark sei stratosferico ! grazie.
Google translate came to my rescue. Thank you. 😁
Awesome Explanation
Thanks, that’s very kind of you to say. 😁
Hats off Mark that is pure awesomeness. The LET was fantastic but then the LAMBDA, too good. But what about poor FILTER function, is your custom formula walking its side of the street? I am wondering what are the pros and cons to a FILTER based formula alternative. ISOMITTED as well when did that come in, nice.
FILTER is based on data order and not the order of the values you've requested, it also includes all values (not just the first / or last), it does not return anything for values that don't exist and it also doesn't fully support wildcard characters.
So, it could be partially possible with FILTER but would be significantly more complex and only part of the solution.
OMG. Stellar tip! Very nice.
Thank you! Cheers!
Superb spreadsheet ❤❤❤ Excel
Thanks, I'm glad you liked it.
Really amazing!!!
Thanks Mark
Wow, thank you 😁
Mark -- in the Lambda version, 2nd example (with headers of Q1, Q2, Q5, it is an error if the items contains an invalid one, like ZZZZ instead of Charlie. It seems to ignore the categories and instead assumes all is OK! As a matter of fact, if you completely clear the items, the array returned doesn't change!
The function is solving the array of arrays problem. It performs a 1 way lookup and returns a 2-day array.
So, yes it is working as it’s meant to.
The additional values are only to help people understand the values returned by the function.
@ but it’s wrong I’d I change the item values in b the left column. They’re not even referenced.
Mark - Love your videos - they are an incredible learning tool. Recently got laid off and thinking of starting up a YT channel (at over 50 it is daunting). I love your video setup/quality. Could you possibly share your equipment/editing/setup? Your lighting is great, and I really like the warm lighting the lamp in the background offers. Sorry for the non-Excel question!
Can you go to exceloffthegrid.com/customer-support
Put your email address in there and then we can continue the conversation on email.
As UA-cam doesn’t provide the best format for longer text. 👍
Thanks a bunch. This will help me a ton. with this method, I hope you could also help replicate the PIVOTBY and GROUPBY functions as they are still not available to old versions of excel.
I'm glad you can use it. In terms of alternatives to GROUPBY/PIVOTBY there are some simple scenarios which can be replicated in other ways. But those formulas are so complex that I wouldn't even try.
Great work!!!
Mark, I am the 300th person in the que of persons who liked it 😅
Great formula of let and lambda use
Awesome! Thank you for clicking the "like" button. I appreciate it.
Superb! SUPERXLOOKUP. Thank you so much.
Thanks, I'm glad you liked it.
Hats off to Mark, excellent formula that i use it to lot extent.
I request you to enhance with one more way where
It lookups h2:h9 in a:a and returns value based on column headder line in hotizantal (q1, q3, q5) lookup.
Hope you get this.
A full column lookup... 🤔
Sounds like you might need the new TRIMRANGE function to become generally available.
you are just great.
Mind blowing 🤯🤯🤯
Lots of advanced techniques in there - hopefully some of them are applicable to other scenarios.
Wow - simply amazing !!!
Awesome Mark, love it
Thanks Chris. 😁
Brilliant!💡
Thanks. 😁
Incredible!!! Thanks a lot!
You're welcome.
Love it. Love. Love it.
Great work, I have one question though. How would avoid the user error? Your assumption is that the list in H12 : H20 is in the exact the same order as in the table. What if a user changes this say the order, then the entire data is not displayed in an orderly manner and gives wrong results. Could on include also the item column to be on the safe side.
Use a HSTACK in the return_array to select specific columns. Or use a formula which gives you a 2-way lookup.
Though, if column order could change it sounds like you’ve got a data issue rather than a formula issue.
pls see my version (5 hours after your comment) it handles that
@@balintbatonyi6546 Thanks nice one, I will analyse it further
Genius 🤯
Amazing! Thanks Mark.
You're welcome. 😁
Thx. Excellent tip !
Thank you.
Thats amazing, thank you, would this be saveable in a personal workbook so you dont have to copy it from a previous workbook?
You could save it in a personal workbook. No issues there. You could easily write a macro to add it to the name manager.
That's really cool!
Thanks. 😁
how do these defined functions work when a workbook is shared and used on another computer?
The function is in the name manager and works like a names range, so it travels with the workbook.
Nice. But is it better than something like this, which can be converted into a nice Lambda function: IFERROR(INDEX(range),XMATCH(),XMATCH()),if_not found)
This doesn't assume you want all columns if you're looking up rows and vice versa.
It’s a slightly different use case.
If you want a 2-way lookup, then INDEX/XMATCH/XMATCH is better.
But, if you want a 1-way lookup that spills in both directions, then I still stand by my solution.
@@ExcelOffTheGrid As an engineer, most of the lookups I've ever had to do require a 2-way lookup; that's why I never even learned VLOOKUP.
I get that you've solved the 2-way spill for a 1-way lookup. I got bogged down trying to use BYROW and REDUCE, and it got ugly.
But if you have both sets of headers anyway (items and quarters), the 2-way lookup gives the same results.
Brilliant!
Thank you.
Really good but the 8th letter of thealphabet appears under A in the dictionary because its name is Aitch and does noy have an aspirated start. Aitch is the name of H.
I know, it’s impressive isn’t it. I can barely string a sentence together, but I can still use Excel like a Ninja. 😁
Is it not possible to use nested filters to do the same?
FILTER has other quirks which make it great from some scenarios but not others.
FILTER is based on data order and not the order of the values you've requested, it also includes all values (not just the first / or last), it does not return anything for values that don't exist and it also doesn't fully support wildcard characters.
So, would be a challenge to make it work in this scenario.
@@ExcelOffTheGrid 👍👍
Super-great video that is action-packed and up-to-speed. Great stuff, Mark!
For those for whom this went too fast: you can always play back the video on half speed… ;-)
FYI: I would’ve added a conceptual line where you explain “why” this works, avoiding the array-within-an-array conundrum.
But maybe I just missed it in the fast-paced action of the video. ;-)
I partly covered that in the last video, so didn't want to go back over that. But definitely anybody who didn't see that might wonder what it was all about.
a fellow excel nerd friend just sent me this with a text that said “Looks like Christmas came early this year!”
🎁 Happy Christmas.
In which case, I try to deliver a bit of Christmas every week on my channel.
Excel geeks unite!!!
Why not use xlookup with & looking for the two arguments?
Because a 1-way lookup spilling across two dimensions is different to a 2-way lookup spilling across two dimensions.
A 2-way lookup is scalar lifting in both directions, while the one-way lookup is scalar lifting in one direction and an array in the other.
So, the use cases are different.
I think the Index function is volatile which could lead to poor performance. I have been using drop(Reduce("", keys, lambda(key, acc, vstack(acc, xloolup(key, in,return)))) ,1) which i think is probably slower but so l at least not volatile
Edit: Index isn't volatile I was thinking of offset
INDEX isn't volatile (you might be thinking of OFFSET), so it should be faster.
@@ExcelOffTheGrid I was getting mixed up with offset! I'll leave the comment since it's a useful alternative technique to this sort of problem (e.g. works with jagged arrays in 2d filter) but I like your formula more for this scenario
Ohhhh ohhhkay! Sweet!
Thanks. 😁
Superxlookup not visible in excel 365. Please guide
In the video, we create a custom function called SUPERXLOOKUP. So, it won't be in you workbook, until you create it.
Genius (or too much time on your hands)!
Thanks (and yes, probably 😂)
In which version is it? 2024 or 365؟
I’m on 365. But should work in 2024 too.
Wow super
Thanks. 😁
I do not really see the point. In my XLOOKUP, it returns the number of columns I choose in return matrix?
But then it doesn’t return multiple lookup values at the same time - it triggers the array of arrays problem.
This solution solves that . That is the point.
@@ExcelOffTheGrid maybe I am misunderstanding you, maybe my English is a little challenged when it comes to Excel (I am from Denmark). I can make a lookup in excel when searching for a particular name, and it can return his salary, address and other columns within a normal XLOOKUP. But this is not what you mean? In the beginning of the video, you show that XLOOKUP only returns one of these values, but mine can return several. I am not trying to criticize your video, I just do not get the same result.
😢 Respect!! This is great
Thank you.
7:07 Not really… The output of the intersection of Alpha and Q3 should be 77, but is returning 82…
Exactly. The horizontal lookup fails. Which is why we then to adjust the formula to handle it.
As an old person who still uses VLOOKUP, is the appeal of writing so many functions that you don’t want to use absolute references and just copy the formulas down and right? Like, this stuff is certainly neat, but it’s also a lot more work. I’m sure I’m missing something here.
Yes, the maintenance element.
When data increases/decreases you have to keep copying your ranges down/right. With dynamic arrays, you don't have to do anything; it all updates itself.
@GusCaravalho I'd say: the size of the file. If you have a formula (100 characters long) for 100 rows, if it is a 1-cell formula, the total weight is 100×100 characters - while for a spill formula it is 1×100 characters (because it actually sits only in the 1st cell. Some my files shrinked by megabytes, and as a consequence they also open faster and refresh faster.
Actually the old-good VLOOKUP spills very well too (if you search many-in-many) and the numeric parameter (for column) is very handy in many cases. And old functions are often faster than the newest - the more rows you have the more you appreciate it.
But I mostly use XLOOKUP. 😊
Waiting for Xlookup Pro Max
1st like 1st comment
First out of the blocks again. 💯
I’m missing something, why not just use an xlookup within the xlookup.
We’re not building a 2 way lookup.
It’s one lookup that spills multiple columns.
The example might look like a 2-way lookup, but that is just to illustrate horizontal & vertical calculation.
Complicated solution.
Why not xloopup per column and lookup value.
What if the number of columns are dynamic?
Heitz 😂😂😂
I have a subscription to MS 365, but I don't see the SuperXLOOKUP function, yet :(
In the video we create a custom function which we have called SUPERXLOOKUP. So you won't ever have it in your Excel, you have to create it.
tactical comment
please dont make EXTRASUPERXLOOKUP
If I were to, I think I would call it SUPERXLOOKUP_PART2_THEREVENGE
You lost me at superXlookup lol
Lots and lots of advanced techniques packed into this one.
@ExcelOffTheGrid I did actually learn more about using lambda than super lookup so it wasn't a total waste lol. Helped me save a bit of time working out employers NI costs
Pls get me a Formula to count meals (breakfasts, lunches & Diners) between 2 dates in different columns
i.e
20/11/2024 Diner to 24/11/24 Breakfast
BF (Column)
4nos
Lunch (Column)
3nos
Diner (Column)
4nos
If you give me a solution i will be very greatful.
That's solveable - I can think of a few ways. I'll add it to the list, maybe I'll do a video one day.
@@ExcelOffTheGrid Thanks ❤️
Great video!! ✌
For fun: SXLKP
=LAMBDA(lv, la, ra, [nf], [mm], [sm],
LET(
x, XMATCH(lv, la, mm, sm),
i, ROWS(lv) > 1,
IFNA(INDEX(ra, IF(i, x, SEQUENCE(ROWS(ra))), IF(i, SEQUENCE(, COLUMNS(ra)), x)), nf)
)
)
Nested is also possible:
=SXLKP(I5:K5,C5:F5,SXLKP(H6:H9,B6:B13,C6:F13,""),"")
Very nice. That's similar to my first version. But decided to go with a longer version in an attempt to make it understandable.
You have to deal with the case of just 1 lookup value. How about another switch parameter (aka ColumnLookup)?
These formulas may be a better alternative -
CHOOSECOLS(Table23,XMATCH(J1:M1,Table23[#Headers],0,1))
CHOOSEROWS(Table23,XMATCH(P11:P19,Table23[SalesMan],0,1))
if XMATCH can’t find a value, it returns #N/A, which causes the entire formula to fail.
So your method only works if all the lookup values exist.