Better than XLOOKUP? Try SUPERXLOOKUP!

[@waitplanwp4129]

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]

Thank you for those kind words. I really appreciate that. 😁

[@vida1719]

What a creative name - SUPERXLOOKUP and great functionality! I think Microsoft should buy this function from you!

[@ExcelOffTheGrid]

Maybe one day they will give is an array-of-arrays compatible version.

[@JoseAntonioMorato]

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) 🤗

[@ExcelOffTheGrid]

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.

[@JoseAntonioMorato]

@@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. 🤗

[@thomasgormanable]

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!

[@ExcelOffTheGrid]

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.

[@Martinrd72]

Just sent the link to this video to my work email. Will be trying this out tomorrow. Great work, exceptional!

[@ExcelOffTheGrid]

Great stuff... give it a go. 👍

[@IvanCortinas_ES]

Tons of talent, Mark. Excellent resolution, which also controls errors. Awesome. Thanks for sharing.

[@ExcelOffTheGrid]

My pleasure! I'm glad you liked it Ivan. 😁

[@kdfarmery]

Mark - this is superb - thank you.

[@Quidisi]

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! 😁

[@ExcelOffTheGrid]

That would be amazing to spill 3 dimensionally... we can dream.

[@weslleyhenrique2989]

Incredible! I love videos that use formulas.

[@ExcelOffTheGrid]

Glad you like them!

[@teoxengineer]

So EXCELlent presentation....It contains everything (XLOOKUP function evaluation from excel team 😉)

[@vinothkumarmanoharan2317]

Awesome Explanation

[@ziggle314]

I have immediate use for this function. Thanks a bunch!

[@ExcelOffTheGrid]

Awesome, I'm glad you can put it to good use.

[@stevereed5776]

Outstanding Mark, what a great solution. Thank you

[@ExcelOffTheGrid]

Thanks, I'm glad you like it.

[@mohammedelsakally540]

Brilliant Mark, Thank you so much 🎉

[@DataVisualisation]

... 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.

[@ExcelOffTheGrid]

I'm still learning all this stuff myself, and it's certainly a lot of fun.

[@DataVisualisation]

@ExcelOffTheGrid ... we can do this together from the next week 😉 ... I sign up for a membership 🏁 ... my way to support you

[@martyc5674]

Nice one Mark- this really would be a great built in function.

[@ExcelOffTheGrid]

Maybe one day Microsoft will add something similar.

[@franzzzis]

incredible.. very impressive, thank you for your work

[@ExcelOffTheGrid]

Thanks, I'm glad you liked it.

[@renatovianello874]

Mark, u are not human! 👏👏👏👏👏

[@ExcelOffTheGrid]

Thanks 😁

[@flaviogarlatticosta]

Mark sei stratosferico ! grazie.

[@ExcelOffTheGrid]

Google translate came to my rescue. Thank you. 😁

[@umarhassan1807]

Can't wait for your next formula. Superb! Thanks a lot.

[@ExcelOffTheGrid]

I might call it NEXTLEVELSUPERXLOOKUP()... or maybe that's a step to far 😂

[@ToanNguyen-q8l6d]

Really amazing!!! Thanks Mark

[@johnhackwood1568]

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.

[@ExcelOffTheGrid]

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.

[@geoffsmile]

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.

[@ExcelOffTheGrid]

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.

[@MounaSammoud-t8d]

this is awesome thanks for sharing Mark 😊☘

[@deepakmirchandani1348]

great job, sir, thanks for such an excellent formula.

[@ExcelOffTheGrid]

You are most welcome - I'm glad you liked it.

[@fongfattchee282]

OMG. Stellar tip! Very nice.

[@ExcelOffTheGrid]

Thank you! Cheers!

[@mrprakashraobs]

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.

[@ExcelOffTheGrid]

A full column lookup... 🤔 Sounds like you might need the new TRIMRANGE function to become generally available.

[@DinoDelight]

Thats amazing, thank you, would this be saveable in a personal workbook so you dont have to copy it from a previous workbook?

[@ExcelOffTheGrid]

You could save it in a personal workbook. No issues there. You could easily write a macro to add it to the name manager.

[@mouhammadwasseemshaabani47]

Brilliant, just brilliant!

[@ExcelOffTheGrid]

Thank you.

[@stefankirst3234]

Incredible!!! Thanks a lot!

[@ExcelOffTheGrid]

You're welcome.

[@nazarkamal8831]

Superb spreadsheet ❤❤❤ Excel

[@ExcelOffTheGrid]

Thanks, I'm glad you liked it.

[@mikeparker3865]

Brilliant!💡

[@ExcelOffTheGrid]

Thanks. 😁

[@martinargimon730]

Thx. Excellent tip !

[@ExcelOffTheGrid]

Thank you.

[@realvatican]

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]

INDEX isn't volatile (you might be thinking of OFFSET), so it should be faster.

[@realvatican]

@@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

[@iankr]

Gor-blimey, this is a bit good! Many thanks, Mark.

[@ExcelOffTheGrid]

Thanks Ian. Loads of advanced techniques in there to keep you happy.

[@zzota]

Amazing! Thanks Mark.

[@ExcelOffTheGrid]

You're welcome. 😁

[@cjkleynhans]

Brilliant!

[@ExcelOffTheGrid]

Thank you.

[@chrism9037]

Awesome Mark, love it

[@ExcelOffTheGrid]

Thanks Chris. 😁

[@gandhisunil3]

Mark, I am the 300th person in the que of persons who liked it 😅 Great formula of let and lambda use

[@ExcelOffTheGrid]

Awesome! Thank you for clicking the "like" button. I appreciate it.

[@rajeshmajumdar4999]

Superb! SUPERXLOOKUP. Thank you so much.

[@ExcelOffTheGrid]

Thanks, I'm glad you liked it.

[@entrustedhub]

😢 Respect!! This is great

[@ExcelOffTheGrid]

Thank you.

[@JoeMeyer8998]

That's really cool!

[@ExcelOffTheGrid]

Thanks. 😁

[@db_mb_69]

Is it not possible to use nested filters to do the same?

[@ExcelOffTheGrid]

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.

[@db_mb_69]

@@ExcelOffTheGrid 👍👍

[@poposhkov]

Why not use xlookup with & looking for the two arguments?

[@ExcelOffTheGrid]

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.

[@henryg5735]

Mind blowing 🤯🤯🤯

[@ExcelOffTheGrid]

Lots of advanced techniques in there - hopefully some of them are applicable to other scenarios.

[@sepideheftekhari333]

In which version is it? 2024 or 365؟

[@ExcelOffTheGrid]

I’m on 365. But should work in 2024 too.

[@RonDavidowicz]

Genius (or too much time on your hands)!

[@ExcelOffTheGrid]

Thanks (and yes, probably 😂)

[@SholaDsdg]

Ohhhh ohhhkay! Sweet!

[@ExcelOffTheGrid]

Thanks. 😁

[@zohrabalimanov009]

Wow super

[@ExcelOffTheGrid]

Thanks. 😁

[@hyperadapted]

Now do superduperxlookup

[@ExcelOffTheGrid]

Ha ha ha - SUPERDUPERXLOOKUP, I love the namee.

[@GeertDelmulle]

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. ;-)

[@ExcelOffTheGrid]

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.

[@JJ_TheGreat]

<a href="#" class="seekto" data-time="427">7:07</a> Not really… The output of the intersection of Alpha and Q3 should be 77, but is returning 82…

[@Al-Ahdal]

1st like 1st comment

[@ExcelOffTheGrid]

First out of the blocks again. 💯

[@Matt-pt3vq]

I’m missing something, why not just use an xlookup within the xlookup.

[@ExcelOffTheGrid]

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.

[@nikolajonovic1172]

please dont make EXTRASUPERXLOOKUP

[@ExcelOffTheGrid]

If I were to, I think I would call it SUPERXLOOKUP_PART2_THEREVENGE

[@grandhebi]

I have a subscription to MS 365, but I don't see the SuperXLOOKUP function, yet :(

[@ExcelOffTheGrid]

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.

[@RichardJones73]

You lost me at superXlookup lol

[@ExcelOffTheGrid]

Lots and lots of advanced techniques packed into this one.

[@RichardJones73]

@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

[@johank4361]

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.

[@ExcelOffTheGrid]

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.

[@johank4361]

@@ExcelOffTheGrid Thanks ❤️

[@Excelambda]

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,""),"")

[@ExcelOffTheGrid]

Very nice. That's similar to my first version. But decided to go with a longer version in an attempt to make it understandable.