DOUBLEXLOOKUP... the Excel function you've been waiting for!

[@bilalsheikh9127]

Bro your content is not for ordinary audience. Your content is of pro+ level quality and for geeks. Your whole work is incredible. Stay blessed.

[@PunmasterSTP]

Would you say that his work is...Excel-lent? 😎

[@IvanCortinas_ES]

I had to squeeze myself tightly into the chair. Spectacular solution. Thanks for this genius, Mark.

[@ExcelOffTheGrid]

Glad you liked it! 😁

[@waitplanwp4129]

the best excel channel in you tube!!! mark ur angel

[@ExcelOffTheGrid]

Wow, thanks! I did wonder of people might be getting bored of this stuff. But hopefully not.

[@lions888lions]

Yeah thats right

[@mr.bachubhaibhigona9090]

I was looking for this double xlookup since long where I can match rows as well as columns. I used a lot of in between formulas to achieve this. Happy to see the neat and clean solution you have provided. I will replace my solution now.

[@chrism9037]

Genius Mark, thank you! Next up: SUPERDUPERXLOOKUP ^2

[@ExcelOffTheGrid]

🤣 I've got another deep dive into another aspect of XLOOKUP next week. Then I think I've done with XLOOKUP for a while.

[@TacereMors]

Fantastic - and thank you.

[@johanneskeller3794]

Thank you very much for the instruction regarding how to create this awesome lamda-function I'm going to use in my daily work . I'm looking forward to your next video in order to keep my excel skills raising !

[@mohammedelsakally540]

How much super xlookup and double xlookup are useful and too much needed for all Excel users who are looking for creativity and development in their work. Thank you very much Mark to share the new ideas that I'm not seen in the other youtube channels. You deserve more 🎉🎉🎉

[@Swizzletwice]

Great video. I never really knew how a function worked or was created. I found that really interesting. Plus I had zero questions at the end. I'll be on the lookout for the EPICXLOOKUP function.😊

[@PunmasterSTP]

I've never gotten that fancy with Excel functions before; this was cool!

[@MohdKhomainiMohdSidik]

Thank you Mark! Brilliantly explained. one of the things that I'm looking for in my daily job

[@merrillmchenry1450]

Brilliant! Now following you. Looking ofrward to more.😃

[@Fredick.7]

x2

[@jeanphilippebagel6414]

Fantastic video, and content in general. Your Formula Magic w/ dynamic arrays book is fantastic as well.

[@ExcelOffTheGrid]

Thanks 😁 Unfortunately, that book is starting to look quite out of date now. Maybe I will update at some point.

[@jeanphilippebagel6414]

@@ExcelOffTheGrid It's still relevant. I wish you wrote one like this on LAMBDA. Your content is great at breaking down the process to make a lambda. Any time there are nested ones, I think the best content is in Excel, showing the various chunks.

[@legolife3012]

Absolute genius. Thanks for sharing this excellent trick

[@williamarthur4801]

To be honest compared to the some of the others I thought that was pretty straightforward, I still have to think about how to use Makearray, which I have say since your vid I have found increasingly useful.

[@iankr]

Great stuff! Many thanks, Mark.

[@peltiertech1879]

Nice example. I wasn't sure about the utility of DOUBLXLOOKUP since INDEX/MATCHX/MATCHX is easy enough to use. But having the Lambda function would save a few keystrokes each time it is used.

[@michaelkuhn6328]

Excellent video!

[@x4ms]

Well done, thank you!

[@ExcelOffTheGrid]

Glad it was helpful! 👍

[@teoxengineer]

Thank you so much for this very very useful tutorial. I would like to learn something about DoubleXlookUp as below: 1- Can we use this Lambda function with Index and Match but not the first argument (Array selection) by using the second argument of Index which is the selection of Arrays of different tables like ----> =INDEX(Ref,Row_Number,[Column_Number],[Area_Number]) 2- Can we create this Lambda function by using "Excel Labs"?

[@ExcelOffTheGrid]

Yes, you definitely could. Here is the formula. =LAMBDA(vlookup_value,vlookup_array,hlookup_value,hlookup_array,return_array,[if_not_found],[vmatch_mode],[vsearch_mode],[hmatch_mode],[hsearch_mode],[area_num], IFERROR(INDEX(return_array, XMATCH(vlookup_value, vlookup_array, vmatch_mode, vsearch_mode), XMATCH(hlookup_value, hlookup_array, hmatch_mode, hsearch_mode), IF(ISOMITTED(area_num), 1, area_num)), IF(ISOMITTED(if_not_found), NA(), if_not_found))) You would need to put the multiple arrays into the return_array argument. See below: =DOUBLEXLOOKUP(H4:H7,B4:B11,I3:J3,C3:F3,(C4:F11,C14:F21),0,,,,,,2) The (C4:F11,C14:F21) is the multiple arrays in the return array.

[@stevereed5776]

Genius, thanks Mark

[@renemarot544]

Hi. Thanks for this. I wonder if it could be even better by deducing the search data area from the vertical header search area and horizontal search area. It will remove a parameter and a source of mistakes.

[@ExcelOffTheGrid]

Yes - that would be possible. However, we would be making an assumption about the shape of the data - which may not always be true, therefore we might need other functions to deal with those. It’s always a balance between easy of use and flexibility.

[@ousmanesidibe951]

very interesting, i needed this formulas like two quarters ago, i had to create a version of my own, it works but doesn't come even close to yours in performance and versatility. thank you for sharing.

[@franzzzis]

fantastic and very helpful!, thanks for sharing your work

[@raymondcole9788]

Thank you! Great Stuff!

[@RJ-lf8ex]

Looking forward to your french press coffee videos

[@JJ_TheGreat]

😂😂😂

EXCELlent, Masterful, Wonderful work. Thank you very much. Question: ¿can I put it in my personal macro book, so that it is by default present in all my books?

[@ExcelOffTheGrid]

Just wait until next week, I will have a video about that 😁

[@Mohamed.GadAllah]

Thanks for the video. Please what is the Custom Formats section in the Home ribbon tab? May you make a video about custom tabs in your Excel? Thanks 😊👍

[@rajeshmajumdar4999]

Superb !!!

[@billhladik406]

Good video. You asked if there were other interesting ways to tackle this problem. Mine relies on the fact that we are looking up 2 things in a grid where the row header in that grid is one of those things and the column header is the other of those things. We don't really care which is which because we are just finding the intersection. So this just takes 4 inputs: item1, item2, the grid (including the row and col headers), and error value if item1&item2 doesn't make sense. item1 is a value found either in the row or col headers and likewise for item2. =LET(item1,G2, item2,H2, rng,$A$1:$E$11, error,-1, ar,IFERROR(XMATCH(item1,TAKE(rng,,1)),-1), ac,IFERROR(XMATCH(item1,TAKE(rng,1)),-1), br,IFERROR(XMATCH(item2,TAKE(rng,,1)),-1), bc,IFERROR(XMATCH(item2,TAKE(rng,1)),-1), row,IF(ar=-1,br,ar), col,IF(ac=-1,bc,ac), IFERROR(INDEX(rng,row,col),error)) This can be easily enhanced if you happen to have the same values in both the row and column headers.

[@MrrrrTony]

Thanks for sharing and for a nice lesson! I'm banging my head on the 255 character limit of Excel 365 Online. Any idea if the character limit on Online version can be removed? It would be a shame to have to shorten the parameter names and provide incomplete info for any user of the DOUBLEXLOOKUP function, or any other longer LAMDBA functions for that matter. Or my only option is to buy the installable version of Office 2021 or 365?

[@andrewloosai1]

thanks for your sharing!!!

[@fletchersjohnson]

Do you know if anyone has done comparisons of the new dynamic array functions? Is using xmatch faster or slower than xlookup? Or is "= A4# & b4#" faster or slower than "=A4:A100 & B4:B100", etc.? Or scan vs byrow. You get the idea.

[@JJ_TheGreat]

6:00 Shouldn't you use the IFNA() function?

[@entrustedhub]

❤ Greetings I just tried it on excel 2019 but it gave me #NAME. I am not quite sure if the system needs update.... Keep doing a good job. 😊 Thank you

[@ExcelOffTheGrid]

Unfortunately, you are versions out of date. It will work on Excel 2024 and Excel 365.

[@entrustedhub]

Thank you

[@mathijs9365]

Basically powerpivot with filters?

[@zm2813]

For Lambda function formula didn't you had to first set up the defined locations in your Name Manager?

[@ExcelOffTheGrid]

LAMBDA functions can be used on the workbook with additional brackets being the values passed into the function. Example: =LAMBDA(xxxxxxxx)("Enter arguments here",1) When they are moved into the name manager you move them without the additional brackets at the end. Then they are available as custom functions.

[@fletchersjohnson]

I am not sure of your question, but here is a guess. If you use =Lambda(a,b, if(a=b,1,5)) you have to add the parameters after, so it would look like =Lambda(a,b, if(a=b,1,5))("alpha", "beta"). So the parameters follow the Lambda() function. BUT - if you put the lambda in via the name manager, you do not need to include the parameters. So you would just put in =Lambda(a,b, if(a=b,1,5)). If you named it foobar, you would reference the parameters when you use foobar as in =foobar("alpha","beta").

[@JJ_TheGreat]

0:33 You mean using INDEX-MATCH-MATCH or INDEX-XMATCH-XMATCH, right?

[@mouhammadwasseemshaabani47]

Great as always, so what is next the great magnificent extra 6 dimensional xlookup :), just for kidding , thank you so much for sharing, however the file is not available yet in the link

[@ExcelOffTheGrid]

Ooops... I forgot to click Save. 🙄 The example file is there now. Thanks for letting me know.

[@evolutionclouds]

So basically Doublexlookup replaces Superxlookup with additional options If we have Doublexlookup in our worksheet, we don’t really need Superxlookup as well, correct?

[@ExcelOffTheGrid]

SUPERXLOOKUP performs a 1-way lookup. One axis is based on a lookup and the second axis is based on the return_array selected within the function. So this is performing XLOOKUP and spilling multiple rows and columns. DOUBLEXLOOKUP performed a 2-way lookup, Both axis are based on lookup values. So this is equivalent to performing XLOOKUP/XLOOKUP and spilling multiple rows and columns. For cross tab data layouts - DOUBLEXLOOKUP is the option to go for. For normalized data layouts - SUPERXLOOKUP is the option to go for using HSTACK to select the relevant columns within the data. DOUBLEXLOOKUP also works in this scenario, but the additional complexity of the second axis will be unnecessary in many scenarios.

[@ToanNguyen-q8l6d]

极好的❤❤

[@kndeepak]

Why not keep the lambda parameter simple for the user. They can supply the whole table. A LET can then decide which the vertical col, horizontal col and body to go into the basic function

[@fletchersjohnson]

You could, but it would be tricky and require a bunch of assumptions. 1) You have to assume that the left most column contains the key values you want. 2) You have to assume that the first row contains the title. You still need to specify the source set of keys (what you want to look up in column 1) and titles (what you want to look up in the title row - especially since these will usually be a different size. On top of that, you would need extra code to then ignore the first column and first row in the output. All that to avoid including one parameter probably isn't worth it.

[@silvanodemetrio407]

You are the best. Impossible to follow your reasoning.

[@skyking2202]

I think you typo'ed that ;)

[@Quidisi]

The Lambda is too much and breaks my tiny brain. The real irony is that when XLOOKUP arrived, I thought I was forever done with INDEX/MATCH, but here I am again, seeing the beauty of INDEX/MATCH.

[@ExcelOffTheGrid]

For a 1 way lookup, XLOOKUP is the way to go, but for a 2 way lookup INDEX/XMATCH/XMATCH is an easier option (in my opinion).

[@davorgolik7873]

I think I will embrace this nice solution!

[@Fardin396]

Isn't Dget function better than this?

[@ExcelOffTheGrid]

The D functions reply on a normalised data structure. But this example is for. Cross-tab structure. So they serve different purposes.

[@siddhantbhuwania8982]

The challenge is when there are duplicate items in lookup array and still want to return unique numbers as a result. Usually it returns the first number for all the duplicate item names. Any way out here?

[@ExcelOffTheGrid]

Then you need the FILTER function. It returns all the matching items. kzbin.info/www/bejne/aGTan4SEpauNntE

[@siddhantbhuwania8982]

@ExcelOffTheGrid So you mean filter would work in the below case? I want to return both 50 & 100 for Excel off grid. Can we use filter in combination with doublexlookup? Name Value Excel off grid 50 Excel off grid 100 Excel on grid 120 Excel 140

[@ExcelOffTheGrid]

You can just use FILTER.

[@siddhantbhuwania8982]

@@ExcelOffTheGrid Ok thanks

[@ramremya1]

What is the difference between superxlookup and doublexlookup

[@ExcelOffTheGrid]

SUPERXLOOKUP spills horizontally and vertically based on a one-way lookup. DOUBLEXLOOKUP spills horizontally and vertically based on a two-way lookup. So the key difference is the number and direction of the lookups.

[@ramremya1]

@ExcelOffTheGrid Thanks

[@Kiyoshi_9606]

*proceeds to make hundreds of LAMBDA() functions as named ranges, copy into a ton of workbooks, and be unsung hero at work* /dream

[@ExcelOffTheGrid]

Unfortunately, your work colleagues won’t care. But you will know that you are an absolute legend!!!

[@lions888lions]

Lambda,, genius ❤

[@mindcache5650]

We. Surely the lookup that AI has been waiting for ....

[@rosemaryng7994]

This should be called superxlookup. The other is less super😂

[@ExcelOffTheGrid]

They great news is you can call them whatever you like. You could even call one of the ROSEMARY and then you could have you own function 😁

[@WaqasAli-dq9wg]

You're living in Fools Paradise

[@ExcelOffTheGrid]

And what a beautiful place it is.

[@petervansoestdevrolijkeboe3320]

Sumif/sumifs seems easier to me…

[@ExcelOffTheGrid]

But they aren’t lookup functions. They are conditional aggregation functions. They: 1) Give the wrong value if you have duplicates 2) Cannot handle text values 3) Can’t work on cross-tab data sets 4) Can’t handle array inputs Don’t get me wrong, SUMIFS is an essential function, but it’s certainly not a replacement for a lookup function.

[@Matt-pt3vq]

Why not just nest xlookup with itself. Much simpler

[@fletchersjohnson]

It sounds simpler, but I can't figure out a way to make that work that actually is simpler. Remember, the number of rows in the destination as well as the number of columns to be included are variable. Even better, it's one lambda function that generates the entire result.

[@TheDavidlloydjones]

I guess you know that this "haitch" stuff is annoying, right?

[@ExcelOffTheGrid]

I thought the "I'll close that bracket and calculate" was the annoying part. 🤣 I recently caught myself saying Amphersand (with an 'f' sound in the middle), rather than Ampersand. What can I say... 40+ years of saying the wrong thing isn't about to change any time soon.

[@karcevgo]

too long and complicated (

[@fletchersjohnson]

Remember - you don't really need to understand it, especially because you are correct - it is really complicated. On the other hand, just copy/paste his lambda into your name manager and then use the function as if MS had simply added that functionality to Excel. As long as it works, does it really matter if you don't understand how it works?

[@josh_excel]

=LAMBDA(vlookup_value,vlookup_array,hlookup_value,hlookup_array,return_array,[if_not_found],[vmatch_mode],[vsearch_mode],[hmatch_mode],[hsearch_mode], IFERROR( INDEX(return_array,XMATCH(vlookup_value,vlookup_array,vmatch_mode,vsearch_mode),XMATCH(hlookup_value,hlookup_array,hmatch_mode,hsearch_mode)), IF(ISOMlTTED(if_not_found),NA(),if_not_found)))

[@Rice0987]

I will wait for triple xlookup😅