Excellent in-depth review of the case studies of the function. These are real cases that appear in everyday life in the company. Thanks for sharing, Mark.
... and again learned something new / refreshed something ... RANGE function ... I hadn't considered using this XLOOKUP combination before. THANK YOU Mark
Excellent video Mark, super helpful! One question: in example # 2, what was the purpose of "-" ? I tried it without that and just using the ampersand on the lookup values and an ampersand on the lookup arrays and it seemed to work fine. I might be missing something (?)
Thanks Chris. Let's suggest you've got lookup values of AA | AAA And you're lookup array has 2 columns as follows from columns with: AAAA | A AAA | AA AA | AAA A | AAAA Without the "-" it will match against all of those values. So we include a spacer character to ensure it only matches against the correct row. The spacer character can be anything which is not found in your data set. It's very much a belt and braces approach, but something I've done for the past 20+ years.
Thank you for the explanation of this very clear function. I find it all very obvious, except fr the last example and looking up in a range. But now i think i get it.
Great to see you mention that it can return a range by use of a colon, BTW really like the vid on a running total using makearray; I can see how it works, but would never have come up with it.
That's interesting. To date I've never had much use for RegEx, so I've not got too excited by it. But you never know, that might change and I'll love it. 😁
@@ExcelOffTheGrid yes ;-) - I just tried to gather the most common patterns into a 'giga-mega' LAMBDA function, kinda 'Swiss army knife' formula, and I was curious how many characters the native name manager can handle 🤣🤣🤣 I was quite surprised by the result. But basically as you wrote, in practice I also used it very, very rarely... the implementation of XLOOKUP and XMATCH surprised me, but I don't see its practical use yet.
We've survived 40 years without RegEx in Excel, so I'm struggling to get excited by it. Maybe I'll have a solid use case for it one day... (then the video will come. 😁)
#3 ... what the heck?! Nobody told me this!! Well, until now! Thanks, Mark! Question: do you think this would be more performant than the standard one-formula-per-row approach, or just more convenient?
Like most things, it depends. From my general testing on arrays a few years ago I would say that: The spilling method is slightly faster for < 10,000 rows The methods are similar for 10,000 - 50,000 rows The individual rows is slightly slower for > 50,000 rows But the convenience benefit is significantly bigger than any performance issue.
Thanks for crystal clear review. Is it possible to combine #3 (scalar lifting) and #8(return multiple value)? When I tried my result is limited to one column result
That's the array of arrays issue. The #8 (return multiple values) is an array and not single values, so when you apply #3 (scalar lifting), you are creating an array of arrays. Excel needs an array of horizontal & vertical values, not an array of arrays. If we use the data in #3, there are lots of ways we could solve that - here are 3 options. #1 =DROP(CHOOSEROWS(Example3,XMATCH(F7:F10,Example3[Item])),,1) #2 =DROP(REDUCE("",F7:F10,LAMBDA(a,v,VSTACK(a,XLOOKUP(v,Example3[Item],Example3[[Size]:[Value]])))),1) #3 =TAKE(SORTBY(Example3[[Size]:[Value]],XMATCH(Example3[Item],F7:F10)),ROWS(F7:F10))
I had to try playing around last night after watching this video, and noticed the same thing. My solution was to make the lookup value and lookup range both absolute, and then just drag the formula to the right. Works great! To clarify I mean setting the first and second arguments of the XLOOKUP to absolute references
Basic Python requires Excel 365. But if you want the full Python or AI there are premium licences you need to purchase in addition. Copilot is an additional $20 per month: www.microsoft.com/en-us/microsoft-365/copilot Full python is an additional $24 per month: www.microsoft.com/en-us/microsoft-365/python-in-excel It starts getting expensive.
Hi Mark, in the 3rd function you show that X- Look up getting multiple values with the formula spilling downwards. but can it spill both ways at the same time. i have been trying to use it in my own office work and alongside choosecols function i am able to get it to spill horizontally but not vertically at the same time. is there any solution for it? Looking forward to your response.
Yes. It's because doing that with XLOOKUP will cause the array of arrays issue. Here are 3 possible solutions to spill in both directions: #1 =DROP(CHOOSEROWS(Example3,XMATCH(F7:F10,Example3[Item])),,1) #2 =DROP(REDUCE("",F7:F10,LAMBDA(a,v,VSTACK(a,XLOOKUP(v,Example3[Item],Example3[[Size]:[Value]])))),1) #3 =TAKE(SORTBY(Example3[[Size]:[Value]],XMATCH(Example3[Item],F7:F10)),ROWS(F7:F10))
Hi, great! Actually in the multi-column --> it is actually just enough to make it only with & --> XLOOKUP (F7&G7;......)..gives the same :) . Also, how did you make in wildcard example that you do not see in the cell the apostrophe, only you see it when you are in the cell.
Well... actually F7&G7 without the spacer character can be a little risky, as it can accidentally match against other combinations. But using a spacer character that doesn't appear in the data, guarantees that this doesn't happen. In regards to the apostrophe... no idea. Excel just did what it does. I didn't do anything special.
It also works in Excel 2021 and Excel 2024. Based on the stats I've seen, the vast majority of users will have access to it. So I think we are well past the point of stating which versions it relates to.
Great video Mark. Never knew all that was possible with XLookup.
The best and comprehensive video about the use of Xlookup function. Thanks Mark for your excellent work 👍❤
Thanks Kebin. Glad you enjoyed it! 😁
Excellent in-depth review of the case studies of the function. These are real cases that appear in everyday life in the company. Thanks for sharing, Mark.
Glad it was helpful! Thanks for watching.
... and again learned something new / refreshed something ... RANGE function ... I hadn't considered using this XLOOKUP combination before. THANK YOU Mark
You're welcome - I'm glad I could jog your memory of range functions.
An excellent summary. I often use Excel with Power BI and some of these combinations will be very useful! ❤
Glad it was helpful!
Excellent video Mark, super helpful! One question: in example # 2, what was the purpose of "-" ? I tried it without that and just using the ampersand on the lookup values and an ampersand on the lookup arrays and it seemed to work fine. I might be missing something (?)
Thanks Chris.
Let's suggest you've got lookup values of AA | AAA
And you're lookup array has 2 columns as follows from columns with:
AAAA | A
AAA | AA
AA | AAA
A | AAAA
Without the "-" it will match against all of those values. So we include a spacer character to ensure it only matches against the correct row.
The spacer character can be anything which is not found in your data set.
It's very much a belt and braces approach, but something I've done for the past 20+ years.
Ah, got it. Thanks Mark
Thank you for the explanation of this very clear function. I find it all very obvious, except fr the last example and looking up in a range. But now i think i get it.
The 2 way lookup is a bit of a 🤯
But if you work through slowly it does make sense... I hope.
Excellent. Thanks for sharing 💥💥💥💥
My pleasure - thanks for watching.
Great uses for XLOOKUP, thank you for your teachings
You're very welcome!
Two way lookup was good. , 👍
Thanks - I hope you can put it to good use. 😁
Some great tips for using XLOOKUP
Glad it was helpful!
it's easy to upstanding, thx for sharing!!!
You are welcome! 😁
Great to see you mention that it can return a range by use of a colon,
BTW really like the vid on a running total using makearray; I can see how it works, but would never have come up with it.
Glad you enjoyed it 😁
Thanks Mark!
(I somehow missed the regex match release, and now I'm amazed :D :D :D :D )
That's interesting. To date I've never had much use for RegEx, so I've not got too excited by it. But you never know, that might change and I'll love it. 😁
@@ExcelOffTheGrid yes ;-) - I just tried to gather the most common patterns into a 'giga-mega' LAMBDA function, kinda 'Swiss army knife' formula, and I was curious how many characters the native name manager can handle 🤣🤣🤣 I was quite surprised by the result. But basically as you wrote, in practice I also used it very, very rarely... the implementation of XLOOKUP and XMATCH surprised me, but I don't see its practical use yet.
Great tips. I was hoping you would explore the REGEX match in XLOOKUP.
We've survived 40 years without RegEx in Excel, so I'm struggling to get excited by it. Maybe I'll have a solid use case for it one day... (then the video will come. 😁)
#3 ... what the heck?! Nobody told me this!! Well, until now! Thanks, Mark! Question: do you think this would be more performant than the standard one-formula-per-row approach, or just more convenient?
Like most things, it depends. From my general testing on arrays a few years ago I would say that:
The spilling method is slightly faster for < 10,000 rows
The methods are similar for 10,000 - 50,000 rows
The individual rows is slightly slower for > 50,000 rows
But the convenience benefit is significantly bigger than any performance issue.
@@ExcelOffTheGrid thank you! I'm showing this to peers today ☺️
That's great 👍
Thanks for crystal clear review. Is it possible to combine #3 (scalar lifting) and #8(return multiple value)? When I tried my result is limited to one column result
That's the array of arrays issue. The #8 (return multiple values) is an array and not single values, so when you apply #3 (scalar lifting), you are creating an array of arrays.
Excel needs an array of horizontal & vertical values, not an array of arrays.
If we use the data in #3, there are lots of ways we could solve that - here are 3 options.
#1
=DROP(CHOOSEROWS(Example3,XMATCH(F7:F10,Example3[Item])),,1)
#2
=DROP(REDUCE("",F7:F10,LAMBDA(a,v,VSTACK(a,XLOOKUP(v,Example3[Item],Example3[[Size]:[Value]])))),1)
#3
=TAKE(SORTBY(Example3[[Size]:[Value]],XMATCH(Example3[Item],F7:F10)),ROWS(F7:F10))
Thanks!!! Been stuck on this for a while....can't wait to try your proposed solutions!!
I had to try playing around last night after watching this video, and noticed the same thing. My solution was to make the lookup value and lookup range both absolute, and then just drag the formula to the right. Works great! To clarify I mean setting the first and second arguments of the XLOOKUP to absolute references
If you want to have the most recent tools like python and AI etc, which specific subscription should you have? Thanks for your time.
Basic Python requires Excel 365. But if you want the full Python or AI there are premium licences you need to purchase in addition.
Copilot is an additional $20 per month: www.microsoft.com/en-us/microsoft-365/copilot
Full python is an additional $24 per month: www.microsoft.com/en-us/microsoft-365/python-in-excel
It starts getting expensive.
Hi Mark,
in the 3rd function you show that X- Look up getting multiple values with the formula spilling downwards. but can it spill both ways at the same time. i have been trying to use it in my own office work and alongside choosecols function i am able to get it to spill horizontally but not vertically at the same time. is there any solution for it? Looking forward to your response.
Yes. It's because doing that with XLOOKUP will cause the array of arrays issue. Here are 3 possible solutions to spill in both directions:
#1
=DROP(CHOOSEROWS(Example3,XMATCH(F7:F10,Example3[Item])),,1)
#2
=DROP(REDUCE("",F7:F10,LAMBDA(a,v,VSTACK(a,XLOOKUP(v,Example3[Item],Example3[[Size]:[Value]])))),1)
#3
=TAKE(SORTBY(Example3[[Size]:[Value]],XMATCH(Example3[Item],F7:F10)),ROWS(F7:F10))
@@ExcelOffTheGrid amazing. Thank you so much. Will definitely try this and let you know.
Hi, great! Actually in the multi-column --> it is actually just enough to make it only with & --> XLOOKUP (F7&G7;......)..gives the same :) . Also, how did you make in wildcard example that you do not see in the cell the apostrophe, only you see it when you are in the cell.
Well... actually F7&G7 without the spacer character can be a little risky, as it can accidentally match against other combinations. But using a spacer character that doesn't appear in the data, guarantees that this doesn't happen.
In regards to the apostrophe... no idea. Excel just did what it does. I didn't do anything special.
Why not preface the video with the fact XLOOKUP only works in Office 365?
It also works in Excel 2021 and Excel 2024. Based on the stats I've seen, the vast majority of users will have access to it. So I think we are well past the point of stating which versions it relates to.
@@ExcelOffTheGrid Fair enough, thanks.
Isn’t 365 the latest version?
It works perfectly in my Excel 2021.
@@mattbowden1981 Yes, but the channel you're on will impact when you get new functions.