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

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

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

[@MohdKhomainiMohdSidik]

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

[@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.😊

[@iankr]

Great stuff! Many thanks, Mark.

[@legolife3012]

Absolute genius. Thanks for sharing this excellent trick

[@merrillmchenry1450]

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

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

[@stevereed5776]

Genius, thanks Mark

[@raymondcole9788]

Thank you! Great Stuff!

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

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

[@franzzzis]

fantastic and very helpful!, thanks for sharing your work

[@RJ-lf8ex]

Looking forward to your french press coffee videos

[@JJ_TheGreat]

😂😂😂

[@rajeshmajumdar4999]

Superb !!!

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

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

[@andrewloosai1]

thanks for your sharing!!!

[@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 😊👍

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

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 😁

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

[@ToanNguyen-q8l6d]

极好的❤❤

[@Xlambda-r6g]

(Excelambda here, noticed that my posts are deleted so tried another account) Great video, like always!! One of the few Excel channels that promote lambdas and this is admirable. The previous SUPERXLOOKUP .didn't do double and the DOUBLEXLOOKUP does not do single 😊 Why not a lambda that can do both. (always use composed lambdas to "uncluster" the parameters, single lambda can be used also) XLKP([vv],[va],[vm],[vs])([hv],[ha],[hm],[hs])((ra,[nf]) =LAMBDA([vv], [va], [vm], [vs], LAMBDA([hv], [ha], [hm], [hs], LAMBDA(ra, [nf], IFNA( INDEX( ra, IF(ISOMITTED(vv), SEQUENCE(ROWS(ra)), XMATCH(vv, va, vm, vs)), IF(ISOMITTED(hv), SEQUENCE(, COLUMNS(ra)), XMATCH(hv, ha, hm, hs)) ), IF(ISOMITTED(nf), NA(), nf) ) ) ) ) - dbl call example works: =XLKP(H4:H7,B4:B11)(I3:J3,C3:F3)(C4:F11,0) - "single" calls examples work also: =XLKP(H4:H7,B4:B11)()(C4:F11,0) =XLKP()(I3:J3,C3:F3)(C4:F11) If we try "single" calls with DOUBLEXLOOKUP will return NA()'s only =DOUBLEXLOOKUP(H4:H7,B4:B11,,,C4:F11) => only errors =DOUBLEXLOOKUP(,,I3:J3,C3:F3,C4:F11) => only errors PS: Only Sunday I will have time to send you the file with multiple dropdowns comparison study ✌😉

[@ExcelOffTheGrid]

I wonder why your posts being deleted? That certainly isn't me. Now my mind is blown by the multiple bracket system you've got going on. Every day is a school day.

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

[@mathijs9365]

Basically powerpivot with filters?

[@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).

[@JJ_TheGreat]

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

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

[@silvanodemetrio407]

You are the best. Impossible to follow your reasoning.

[@skyking2202]

I think you typo'ed that ;)

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

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

[@JJ_TheGreat]

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

[@lions888lions]

Lambda,, genius ❤

[@Matt-pt3vq]

Why not just nest xlookup with itself. Much simpler

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

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

[@Rice0987]

I will wait for triple xlookup😅