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!
Hello Mark, thank you for the inspiration to think further factory functions. Also mentioning Sandor Debreceni due to our meaningful conversations. My version 1) needs no table format source, 2) only takes one argument, which is the full source range and 3) shows missing lookup values with two different error handling icons (missing row lookup 🔙 and missing column lookup 🔝). For adding as own formula, paste this: =LAMBDA(fullsource;LET(r;ROW();c;COLUMN(); FirstRow;ADDRESS(r;c-1;4;1); FirstCol;ADDRESS(r-1;c;4;1); LastRow;XMATCH(FALSE;ISTEXT(INDIRECT(FirstRow&":"&ADDRESS(999999;c-1;4;1)));0)-1; LastCol;XMATCH(FALSE;ISTEXT(INDIRECT(FirstCol&":"&ADDRESS(r-1;15999;4;1)));0)-1; VerticalHeader;INDIRECT(FirstRow&":"&ADDRESS(r+LastRow-1;c-1;4;1)); HorizontHeader;INDIRECT(FirstCol&":"&ADDRESS(r-1;c+LastCol-1;4;1)); MatchRows;IFERROR(XMATCH(VerticalHeader;CHOOSECOLS(fullsource;1);0);1/0); MatchCols;XMATCH(HorizontHeader;CHOOSEROWS(fullsource;1);0); MAP(INDEX(fullsource;MatchRows;MatchCols);LAMBDA(x;IFERROR(SWITCH(ERROR.TYPE(x);2;"🔙";7;"🔝");x)))))
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!
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) 🤗
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.
@@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. 🤗
... 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.
Mark - Love your videos - they are an incredible learning tool. Recently got laid off and thinking of starting up a YT channel (at over 50 it is daunting). I love your video setup/quality. Could you possibly share your equipment/editing/setup? Your lighting is great, and I really like the warm lighting the lamp in the background offers. Sorry for the non-Excel question!
Can you go to exceloffthegrid.com/customer-support Put your email address in there and then we can continue the conversation on email. As UA-cam doesn’t provide the best format for longer text. 👍
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.
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.
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.
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. ;-)
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.
Mark -- in the Lambda version, 2nd example (with headers of Q1, Q2, Q5, it is an error if the items contains an invalid one, like ZZZZ instead of Charlie. It seems to ignore the categories and instead assumes all is OK! As a matter of fact, if you completely clear the items, the array returned doesn't change!
The function is solving the array of arrays problem. It performs a 1 way lookup and returns a 2-day array. So, yes it is working as it’s meant to. The additional values are only to help people understand the values returned by the function.
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.
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.
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.
Great work, I have one question though. How would avoid the user error? Your assumption is that the list in H12 : H20 is in the exact the same order as in the table. What if a user changes this say the order, then the entire data is not displayed in an orderly manner and gives wrong results. Could on include also the item column to be on the safe side.
Use a HSTACK in the return_array to select specific columns. Or use a formula which gives you a 2-way lookup. Though, if column order could change it sounds like you’ve got a data issue rather than a formula issue.
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.
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 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
Really good but the 8th letter of thealphabet appears under A in the dictionary because its name is Aitch and does noy have an aspirated start. Aitch is the name of H.
But then it doesn’t return multiple lookup values at the same time - it triggers the array of arrays problem. This solution solves that . That is the point.
@@ExcelOffTheGrid maybe I am misunderstanding you, maybe my English is a little challenged when it comes to Excel (I am from Denmark). I can make a lookup in excel when searching for a particular name, and it can return his salary, address and other columns within a normal XLOOKUP. But this is not what you mean? In the beginning of the video, you show that XLOOKUP only returns one of these values, but mine can return several. I am not trying to criticize your video, I just do not get the same result.
As an old person who still uses VLOOKUP, is the appeal of writing so many functions that you don’t want to use absolute references and just copy the formulas down and right? Like, this stuff is certainly neat, but it’s also a lot more work. I’m sure I’m missing something here.
Yes, the maintenance element. When data increases/decreases you have to keep copying your ranges down/right. With dynamic arrays, you don't have to do anything; it all updates itself.
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.
@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
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.
Nice. But is it better than something like this, which can be converted into a nice Lambda function: IFERROR(INDEX(range),XMATCH(),XMATCH()),if_not found) This doesn't assume you want all columns if you're looking up rows and vice versa.
It’s a slightly different use case. If you want a 2-way lookup, then INDEX/XMATCH/XMATCH is better. But, if you want a 1-way lookup that spills in both directions, then I still stand by my solution.
@@ExcelOffTheGrid As an engineer, most of the lookups I've ever had to do require a 2-way lookup; that's why I never even learned VLOOKUP. I get that you've solved the 2-way spill for a 1-way lookup. I got bogged down trying to use BYROW and REDUCE, and it got ugly. But if you have both sets of headers anyway (items and quarters), the 2-way lookup gives the same results.
These formulas may be a better alternative - CHOOSECOLS(Table23,XMATCH(J1:M1,Table23[#Headers],0,1)) CHOOSEROWS(Table23,XMATCH(P11:P19,Table23[SalesMan],0,1))
What a creative name - SUPERXLOOKUP and great functionality! I think Microsoft should buy this function from you!
Maybe one day they will give is an array-of-arrays compatible version.
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!
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.
This really is off the grid 💪🏼
Hello Mark, thank you for the inspiration to think further factory functions. Also mentioning Sandor Debreceni due to our meaningful conversations. My version 1) needs no table format source, 2) only takes one argument, which is the full source range and 3) shows missing lookup values with two different error handling icons (missing row lookup 🔙 and missing column lookup 🔝). For adding as own formula, paste this: =LAMBDA(fullsource;LET(r;ROW();c;COLUMN(); FirstRow;ADDRESS(r;c-1;4;1); FirstCol;ADDRESS(r-1;c;4;1); LastRow;XMATCH(FALSE;ISTEXT(INDIRECT(FirstRow&":"&ADDRESS(999999;c-1;4;1)));0)-1; LastCol;XMATCH(FALSE;ISTEXT(INDIRECT(FirstCol&":"&ADDRESS(r-1;15999;4;1)));0)-1; VerticalHeader;INDIRECT(FirstRow&":"&ADDRESS(r+LastRow-1;c-1;4;1)); HorizontHeader;INDIRECT(FirstCol&":"&ADDRESS(r-1;c+LastCol-1;4;1)); MatchRows;IFERROR(XMATCH(VerticalHeader;CHOOSECOLS(fullsource;1);0);1/0); MatchCols;XMATCH(HorizontHeader;CHOOSEROWS(fullsource;1);0); MAP(INDEX(fullsource;MatchRows;MatchCols);LAMBDA(x;IFERROR(SWITCH(ERROR.TYPE(x);2;"🔙";7;"🔝");x)))))
Incredible!
I love videos that use formulas.
Glad you like them!
Just sent the link to this video to my work email. Will be trying this out tomorrow. Great work, exceptional!
Great stuff... give it a go. 👍
Tons of talent, Mark. Excellent resolution, which also controls errors. Awesome. Thanks for sharing.
My pleasure! I'm glad you liked it Ivan. 😁
I have immediate use for this function. Thanks a bunch!
Awesome, I'm glad you can put it to good use.
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!
Thank you for those kind words. I really appreciate that. 😁
As usual, you are always EXCELLing yourself
Thanks, I appreciate that. 😁
Can't wait for your next formula. Superb! Thanks a lot.
I might call it NEXTLEVELSUPERXLOOKUP()... or maybe that's a step to far 😂
Outstanding Mark, what a great solution. Thank you
Thanks, I'm glad you like it.
Thank you so much for this. I have this urge to learn more about Excel!
Nice one Mark- this really would be a great built in function.
Maybe one day Microsoft will add something similar.
Mark - this is superb - thank you.
You’re welcome - I’m glad you liked it.
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! 😁
That would be amazing to spill 3 dimensionally... we can dream.
incredible.. very impressive, thank you for your work
Thanks, I'm glad you liked it.
So EXCELlent presentation....It contains everything (XLOOKUP function evaluation from excel team 😉)
Thanks, I’m glad you liked it.
Mark, u are not human!
👏👏👏👏👏
Thanks 😁
Brilliant Mark, Thank you so much 🎉
My pleasure! 😁
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) 🤗
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.
@@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. 🤗
Gor-blimey, this is a bit good! Many thanks, Mark.
Thanks Ian. Loads of advanced techniques in there to keep you happy.
great job, sir, thanks for such an excellent formula.
You are most welcome - I'm glad you liked it.
this is awesome thanks for sharing Mark 😊☘
My pleasure - I’m glad you liked it.
OMG. Stellar tip! Very nice.
Thank you! Cheers!
Really amazing!!!
Thanks Mark
Wow, thank you 😁
Mark sei stratosferico ! grazie.
Google translate came to my rescue. Thank you. 😁
Brilliant, just brilliant!
Thank you.
Awesome Explanation
Thanks, that’s very kind of you to say. 😁
Superb spreadsheet ❤❤❤ Excel
Thanks, I'm glad you liked it.
Mark,
This is awesome ❤
In your next video, please do it using Power Query.
Thanks in advance 😅
... 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.
I'm still learning all this stuff myself, and it's certainly a lot of fun.
@ExcelOffTheGrid ... we can do this together from the next week 😉 ... I sign up for a membership 🏁 ... my way to support you
😢 Respect!! This is great
Thank you.
Wow - simply amazing !!!
Incredible!!! Thanks a lot!
You're welcome.
Awesome Mark, love it
Thanks Chris. 😁
Brilliant!💡
Thanks. 😁
Mark, I am the 300th person in the que of persons who liked it 😅
Great formula of let and lambda use
Awesome! Thank you for clicking the "like" button. I appreciate it.
Amazing! Thanks Mark.
You're welcome. 😁
Superb! SUPERXLOOKUP. Thank you so much.
Thanks, I'm glad you liked it.
Mark - Love your videos - they are an incredible learning tool. Recently got laid off and thinking of starting up a YT channel (at over 50 it is daunting). I love your video setup/quality. Could you possibly share your equipment/editing/setup? Your lighting is great, and I really like the warm lighting the lamp in the background offers. Sorry for the non-Excel question!
Can you go to exceloffthegrid.com/customer-support
Put your email address in there and then we can continue the conversation on email.
As UA-cam doesn’t provide the best format for longer text. 👍
That's really cool!
Thanks. 😁
Thx. Excellent tip !
Thank you.
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.
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.
Genius 🤯
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.
A full column lookup... 🤔
Sounds like you might need the new TRIMRANGE function to become generally available.
Mind blowing 🤯🤯🤯
Lots of advanced techniques in there - hopefully some of them are applicable to other scenarios.
Brilliant!
Thank you.
Love it. Love. Love it.
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. ;-)
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.
Mark -- in the Lambda version, 2nd example (with headers of Q1, Q2, Q5, it is an error if the items contains an invalid one, like ZZZZ instead of Charlie. It seems to ignore the categories and instead assumes all is OK! As a matter of fact, if you completely clear the items, the array returned doesn't change!
The function is solving the array of arrays problem. It performs a 1 way lookup and returns a 2-day array.
So, yes it is working as it’s meant to.
The additional values are only to help people understand the values returned by the function.
@ but it’s wrong I’d I change the item values in b the left column. They’re not even referenced.
a fellow excel nerd friend just sent me this with a text that said “Looks like Christmas came early this year!”
🎁 Happy Christmas.
In which case, I try to deliver a bit of Christmas every week on my channel.
Excel geeks unite!!!
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.
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.
Is it not possible to use nested filters to do the same?
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.
@@ExcelOffTheGrid 👍👍
Thats amazing, thank you, would this be saveable in a personal workbook so you dont have to copy it from a previous workbook?
You could save it in a personal workbook. No issues there. You could easily write a macro to add it to the name manager.
Ohhhh ohhhkay! Sweet!
Thanks. 😁
Great work, I have one question though. How would avoid the user error? Your assumption is that the list in H12 : H20 is in the exact the same order as in the table. What if a user changes this say the order, then the entire data is not displayed in an orderly manner and gives wrong results. Could on include also the item column to be on the safe side.
Use a HSTACK in the return_array to select specific columns. Or use a formula which gives you a 2-way lookup.
Though, if column order could change it sounds like you’ve got a data issue rather than a formula issue.
pls see my version (5 hours after your comment) it handles that
@@balintbatonyi6546 Thanks nice one, I will analyse it further
how do these defined functions work when a workbook is shared and used on another computer?
The function is in the name manager and works like a names range, so it travels with the workbook.
Superxlookup not visible in excel 365. Please guide
In the video, we create a custom function called SUPERXLOOKUP. So, it won't be in you workbook, until you create it.
Genius (or too much time on your hands)!
Thanks (and yes, probably 😂)
Wow super
Thanks. 😁
In which version is it? 2024 or 365؟
I’m on 365. But should work in 2024 too.
Why not use xlookup with & looking for the two arguments?
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.
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
INDEX isn't volatile (you might be thinking of OFFSET), so it should be faster.
@@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
Really good but the 8th letter of thealphabet appears under A in the dictionary because its name is Aitch and does noy have an aspirated start. Aitch is the name of H.
I know, it’s impressive isn’t it. I can barely string a sentence together, but I can still use Excel like a Ninja. 😁
I do not really see the point. In my XLOOKUP, it returns the number of columns I choose in return matrix?
But then it doesn’t return multiple lookup values at the same time - it triggers the array of arrays problem.
This solution solves that . That is the point.
@@ExcelOffTheGrid maybe I am misunderstanding you, maybe my English is a little challenged when it comes to Excel (I am from Denmark). I can make a lookup in excel when searching for a particular name, and it can return his salary, address and other columns within a normal XLOOKUP. But this is not what you mean? In the beginning of the video, you show that XLOOKUP only returns one of these values, but mine can return several. I am not trying to criticize your video, I just do not get the same result.
1st like 1st comment
First out of the blocks again. 💯
Waiting for Xlookup Pro Max
As an old person who still uses VLOOKUP, is the appeal of writing so many functions that you don’t want to use absolute references and just copy the formulas down and right? Like, this stuff is certainly neat, but it’s also a lot more work. I’m sure I’m missing something here.
Yes, the maintenance element.
When data increases/decreases you have to keep copying your ranges down/right. With dynamic arrays, you don't have to do anything; it all updates itself.
I have a subscription to MS 365, but I don't see the SuperXLOOKUP function, yet :(
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.
7:07 Not really… The output of the intersection of Alpha and Q3 should be 77, but is returning 82…
Exactly. The horizontal lookup fails. Which is why we then to adjust the formula to handle it.
please dont make EXTRASUPERXLOOKUP
If I were to, I think I would call it SUPERXLOOKUP_PART2_THEREVENGE
tactical comment
Heitz 😂😂😂
I’m missing something, why not just use an xlookup within the xlookup.
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.
Complicated solution.
Why not xloopup per column and lookup value.
What if the number of columns are dynamic?
You lost me at superXlookup lol
Lots and lots of advanced techniques packed into this one.
@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
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.
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.
@@ExcelOffTheGrid Thanks ❤️
Nice. But is it better than something like this, which can be converted into a nice Lambda function: IFERROR(INDEX(range),XMATCH(),XMATCH()),if_not found)
This doesn't assume you want all columns if you're looking up rows and vice versa.
It’s a slightly different use case.
If you want a 2-way lookup, then INDEX/XMATCH/XMATCH is better.
But, if you want a 1-way lookup that spills in both directions, then I still stand by my solution.
@@ExcelOffTheGrid As an engineer, most of the lookups I've ever had to do require a 2-way lookup; that's why I never even learned VLOOKUP.
I get that you've solved the 2-way spill for a 1-way lookup. I got bogged down trying to use BYROW and REDUCE, and it got ugly.
But if you have both sets of headers anyway (items and quarters), the 2-way lookup gives the same results.
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,""),"")
Very nice. That's similar to my first version. But decided to go with a longer version in an attempt to make it understandable.
You have to deal with the case of just 1 lookup value. How about another switch parameter (aka ColumnLookup)?
These formulas may be a better alternative -
CHOOSECOLS(Table23,XMATCH(J1:M1,Table23[#Headers],0,1))
CHOOSEROWS(Table23,XMATCH(P11:P19,Table23[SalesMan],0,1))
if XMATCH can’t find a value, it returns #N/A, which causes the entire formula to fail.
So your method only works if all the lookup values exist.