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
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.
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.
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. 😁)
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.
Because without the space, it can lead to the wrong result. If our lookup_range has the following values ColA ColB A1 | 11 A11 | 1 What happens if our lookup is "A11"&"1"? It will match against the first row - which is wrong. But if you we a spacer character, it creates "A11-1". Therefore it can only match against row 2. Which is the correct result. Therefore, anytime I combine columns for comparison, I always include a spacer character which does not appear in the data. It prevents potential errors.
Dear Mark. In the "2-Multi-column lookup" example, you can also use the following method: =XLOOKUP(1,((F7=Example2[Item])*(G7=Example2[Size])),Example2[Value],0) 🤗
Great Mark. Quick question: If you lookup multiple instances like this formula: =XLOOKUP(A4:A6,M4:M1323,N4:O1323), XLOOKUP returns only a single column in this case the column N, not an array. But, if you use XLOOKUP like this by choosing a single column as lookup value: =XLOOKUP(A4,M4:M1323,N4:O1323) then you drill down the formula, it returns for each line two columns as return array. This is bit strange since the first formula work perfect when you select only a single column as lookup value (in this case it returns a array). Any idea? Thanks
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
#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.
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, 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.
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.
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))
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.
