Excel...lent and PQ-lent ! I knew about possibility to join first and second arguments on XLOOKUP, this makes this function more powerful than previous mega-star function VLOOKUP. For first XLOOKUP you used in fact its internal "HLOOKUP", and this makes XLOOKUP even stronger. Greetings from Romania !
Yes, your insights are good: XLOOKUP can do so many things all in one function - that we used to have to do in crazy other ways with multiple types of functions.
Good stuff as usual. I played with a more generalized n-way lookup. Here you can have as many row or column headers as needed. =LET(lookfor,G3:G5, matrix,A2:E76, grid,IF(ISNUMBER(MATCH(matrix,lookfor,0)),1,0), pick,MAKEARRAY(ROWS(grid),COLUMNS(grid), LAMBDA(row,col, LET( vert,SUM(CHOOSECOLS(TAKE(grid,row),col)), horz,SUM(CHOOSEROWS(TAKE(grid,,col),row)), gcell,INDEX(grid,row,col), mcell,INDEX(matrix,row,col), IF(AND(gcell=0,horz>0,vert>0,horz+vert=COUNTA(lookfor)),mcell,"")))), list,TOCOL(pick,3), FILTER(list,list"","")) Lookfor can be a list n headers to match in matrix and they can come from rows or columns. You do not need to specify where they are found.The intersection cells will be returned in a list. There might be just 1 or could be several.
Thank you very much for that clever "make it easy to specify conditions" Formula, @billhladik406!!! I have added it to the download workbook so others can see.
@@excelisfun I used to add a helper column where i combine those fields together and perform an xlookup that way. But it looks like I don’t have to do that additional step anymore.
I vote for power query over formula It is easy and good when the data is large Great video Will help me in my reporting stuff when there are connections between two tables Thanks for the video
Yes: INDEX Old School!!!!!!!! INDEX is still perfect for row and column positions and can do some real magic with arrays of row and column positions!!! It really is one of the best examples of Old School and New School Magic working together. No need for MAP, LAMBDA and others... I have added the formula to the "Go Team!" download : ) : )
@@excelisfun Thanks!! Please forgive the long post. My humble opinion on the matter for anybody interested in lambdas in general: There are some stages of the learning process we have to cover to become better lambda "programmers" in the context of the new lambda helper functions: - 1st stage, Test them extensively, imagine the simplest tasks posible playing with all arguments. - 2nd stage, Use them to solve more complicated tasks, tasks we were familiar with, or we solved before with old techniques using the real estate of the spreadsheet, drag and drops, helper columns, expandable ranges, or any range formulas techniques. -3rd stage, When we became familiar with them, using them everywhere and for everything. - 4th stage, Avoid overuse of them and Algorithm Awareness. They are all iterative functions. Iterations tend to take time for large arrays. Like exact match vs approximate match. We have to use them Only if there is no other way to solve the same task "in bulk" dynamic arrays. Always be aware of the Big O (time/space complexity of an algorithm) . Check any refine final solution based on simulated larger data samples. If there is no "in bulk" dynamic solution and we have to use iterations, always use the solution with smallest nr. of iterations. Examples: 1. Removing all digits in an array of strings. (task from the previos video) 1st solution - for each cell, textsplit for digits and concat the result, using MAP. Total iterations = rows*clms initial array. 2nd solution - using substitute each digit for entire array, with REDUCE. Total iterations no matter of the array size = 10 iterations. Obvious the best solution. 2. When 2 solutions are possible, iterating by rows or by columns, choose the shorter dimension. 3. Nested lambda helper functions , exponentially slow. Only when no other way. 4. A long classic many variable formulae can be super fast compared with a super elegant short lambda helper alternative. - 5th stage, Vision vs lack of Vision. Modular design versatility. Any lambda should be designed as a probable future subrutine used by other function, therefore a golden rule for lambda "programmers": always avoid any construction using any functions that have range or reference as arguments, like ...xxIFS , OFFSET, ROW etc.. - 6th stage. Sky is the limit. Build our own custom-made lambda helper functions environment. I promise you that if we get here, with only a couple of lambdas we can solve an overly complex spectrum of tasks. This is my personal opinion based on my experience so far and I wanted to share it. ✌😉
@@excelisfun Why this divagation? Many of us are, or will be, or have been, to stage 3. It is a very important stage in the process, that's why I want to salute any solution that uses lambda helper functions, no matter if they will be ever use in real life or not, if are the best one or not, short or long, eficient or slow. What is important for all of us , is that these solutions can hold clever design tricks or concepts techniques that can be useful in other designes where they will be the Only alternative. My 2 cents. ✌
@@Excelambda LOVE^Efficiency * 100000000 : ) : ) : ) : ) : ) But, hey? Why Here? Let's put it as a pinned comment before the LAMBDA video! I will do that now, of if you post it, I will delete mine and pin yours.
Thanks, Amazing as always, but please don't forget old-school students :) below is my suggestion: =VLOOKUP(S[@Product]&S[@SR],IF({1,0},L[Product]&L[SR],INDEX(L,,MATCH(S[@State],L[#Headers],0))),2,0) Also for the new school students following formula is my suggestion: =FILTER(FILTER(L,(L[Product]=S[@Product])*(L[SR]=S[@SR])),COUNTIF(S[@State],L[#Headers])) Finally, to solve this issue by custom column in my lovely feature (Power Query), the M formula can be this: =Table.ToList(Table.SelectColumns(Table.SelectRows(IdLookup,(x)=>[Product]=x[Product] and [SR]=x[SR]),[State])){0}
Thanks, Teammate! I Added worksheet formulas to download. I stopped doing M Code lookup formulas a while back, cuz they just always seem so inefficient. But I used to use them a lot : )
Just another approach for exercise: ⇨ filter (find) Row by composite key; ⇩ select the appropriate Column =INDEX( FILTER(L, (B10=L[Product])*(C10=L[SR])), XMATCH(D10,L[#Headers]))
The same way to solve in Clip with the addition MAP, =MAP(S[Product],S[SR],S[State],LAMBDA(b,c,d, XLOOKUP(b&c,L[Product]&L[SR], XLOOKUP(d,D3:F3,L[[Washington]:[California]]))))
One can use this combination too for solving this situation. =XLOOKUP( 1, COUNTIFS(S[@Product], L[Product], S[@SR], L[SR]), XLOOKUP( S[@State], L[[#Headers],[Washington]:[California]], L[[Washington]:[California]] ) ) Actually, I love this one more. With this one, one doesn't need to use single-row values one by one. =XLOOKUP( TRUE, BYROW(S[@[Product]:[SR]] = L[[Product]:[SR]], LAMBDA(a, AND(a))), XLOOKUP( S[@State], L[[#Headers],[Washington]:[California]], L[[Washington]:[California]] ) )
@@JonathanExcels RUclips calls them "handles". They were supposed to send all RUclipsrs messages about the impending change. Maybe you did not get it. I think it is easy to change somewhere in settings...
Thank you Sir.. Awesome ..I have a question when I tried to spill the result , It giving the wrong answer apart from the 1st cell. Instead of using B10&C10 look up value and dragging it down. I've tried B10:B15&C10:C15.
If you want to spill, try this formula: =INDEX(L,XMATCH(S[Product]&S[SR],L[Product]&L[SR]),XMATCH(S[State],L[#Headers])) I don't know how to spill directly with XLOOKUP. I did a video on two way lookup spill here: ruclips.net/video/MNMVIIdK40U/видео.html But the INDEX is really the best.
Power query tends to unsort the original fact table sort order. Consequently, you have to add an index column to keep the original sorted order. I wonder why you lose the original sort in the fact table?
Could you use this as a way to utilize hierarchies? Meaning could you use to populate income statement based on different departments? Or even to pick which level of an income statement to go to ten thousand foot view, next level, next level?
You could also do this the "old-fashioned" way which would work in the new world as is and in the old-world using CTRL+SHIFT+ENTER to commit it... =INDEX(B$4:F$7,MATCH(B10&C10,B$4:B$7&C$4:C$7,0),MATCH(D10,B$3:F$3,0))
Hello, my friend, there is a problem that I want to solve related to making a hyperlink to data in the same sheet, but I want it inside a drop-down list so that when clicking on any name inside the drop-down list, it goes directly to the location of the cell that contains the same name, for example the cell that I want to go to is located in column AA2 and the drop-down list in column A1 and how I can contact with you if you want the sheet work that I use
I need to know how to do something in Excel. I have weekly spreadsheets that name staff multiple times each week. So, say Mike is on this week's spreadsheet 3 times. And Susie's name is on the spreadsheet 3 times. And Mike is on last week's spreadsheet 5 times. I want to know how to summarize Mike's name so the total is one for the weeks he is on the list. As well as Susie equaling 1 for the weeks she is on the list. Then I need to add the 52 weeks of spreadsheets names and how many times that person was on a list throughout the year. Again, no matter how many times their name is on a list each week it only counts as 1 time. So if Mike was on the list 42 weeks out of the year how do I easily get that number? Without physically counting them each week. At times I have 35 peoples names on a spreadsheet each week but many of them are on there 2. 3. 4 times. Probably a ridiculous question but I really don't want to physically add up 52 weeks of data by hand. Thanks in advance.
Excel...lent and PQ-lent ! I knew about possibility to join first and second arguments on XLOOKUP, this makes this function more powerful than previous mega-star function VLOOKUP. For first XLOOKUP you used in fact its internal "HLOOKUP", and this makes XLOOKUP even stronger. Greetings from Romania !
Yes, your insights are good: XLOOKUP can do so many things all in one function - that we used to have to do in crazy other ways with multiple types of functions.
Good stuff as usual. I played with a more generalized n-way lookup. Here you can have as many row or column headers as needed.
=LET(lookfor,G3:G5,
matrix,A2:E76,
grid,IF(ISNUMBER(MATCH(matrix,lookfor,0)),1,0),
pick,MAKEARRAY(ROWS(grid),COLUMNS(grid),
LAMBDA(row,col,
LET(
vert,SUM(CHOOSECOLS(TAKE(grid,row),col)),
horz,SUM(CHOOSEROWS(TAKE(grid,,col),row)),
gcell,INDEX(grid,row,col),
mcell,INDEX(matrix,row,col),
IF(AND(gcell=0,horz>0,vert>0,horz+vert=COUNTA(lookfor)),mcell,"")))),
list,TOCOL(pick,3),
FILTER(list,list"",""))
Lookfor can be a list n headers to match in matrix and they can come from rows or columns. You do not need to specify where they are found.The intersection cells will be returned in a list. There might be just 1 or could be several.
Thank you very much for that clever "make it easy to specify conditions" Formula, @billhladik406!!! I have added it to the download workbook so others can see.
Boom!Great Class To Start 2023...Thank You Mike :)
Yes, Happy 2023 Bike Brother darryl!!!!!!
both of your books are gold. thanks man
Glad you like the books : ) With two gold books do you have? I have published 3.
Thanks very much for merge query used more than one colume.
You are welcome! Power Query is amazing : )
Thanks Mike!! This helped alot. I took me more steps to accomplish what you just did, but this is so much more efficient!
Great! Glad it helps! What steps did you use?
@@excelisfun I used to add a helper column where i combine those fields together and perform an xlookup that way. But it looks like I don’t have to do that additional step anymore.
@@renzz-oo I have done that also, but a lot of times, this is neater : )
I vote for power query over formula
It is easy and good when the data is large
Great video
Will help me in my reporting stuff when there are connections between two tables
Thanks for the video
You are welcome, Vishal!!!!!!
Nice one MIke! Thanks for the multiple solutions. Thumbs up!!
You are welcome for the double fun, Wayne!!!! You knew both of these already, you smart Exceller ; )
Great Video!! ✌😉
single cell :
=INDEX(L,XMATCH(S[Product]&S[SR],L[Product]&L[SR]),XMATCH(S[State],L[#Headers]))
Yes: INDEX Old School!!!!!!!! INDEX is still perfect for row and column positions and can do some real magic with arrays of row and column positions!!! It really is one of the best examples of Old School and New School Magic working together. No need for MAP, LAMBDA and others... I have added the formula to the "Go Team!" download : ) : )
@@excelisfun Thanks!!
Please forgive the long post. My humble opinion on the matter for anybody interested in lambdas in general:
There are some stages of the learning process we have to cover to become better lambda "programmers" in the context of the new lambda helper functions:
- 1st stage, Test them extensively, imagine the simplest tasks posible playing with all arguments.
- 2nd stage, Use them to solve more complicated tasks, tasks we were familiar with, or we solved before with old techniques using the real estate of the spreadsheet, drag and drops, helper columns, expandable ranges, or any range formulas techniques.
-3rd stage, When we became familiar with them, using them everywhere and for everything.
- 4th stage, Avoid overuse of them and Algorithm Awareness. They are all iterative functions. Iterations tend to take time for large arrays. Like exact match vs approximate match. We have to use them Only if there is no other way to solve the same task "in bulk" dynamic arrays. Always be aware of the Big O (time/space complexity of an algorithm) . Check any refine final solution based on simulated larger data samples.
If there is no "in bulk" dynamic solution and we have to use iterations, always use the solution with smallest nr. of iterations.
Examples:
1. Removing all digits in an array of strings. (task from the previos video)
1st solution - for each cell, textsplit for digits and concat the result, using MAP. Total iterations = rows*clms initial array.
2nd solution - using substitute each digit for entire array, with REDUCE. Total iterations no matter of the array size = 10 iterations. Obvious the best solution.
2. When 2 solutions are possible, iterating by rows or by columns, choose the shorter dimension.
3. Nested lambda helper functions , exponentially slow. Only when no other way.
4. A long classic many variable formulae can be super fast compared with a super elegant short lambda helper alternative.
- 5th stage, Vision vs lack of Vision. Modular design versatility. Any lambda should be designed as a probable future subrutine used by other function, therefore a golden rule for lambda "programmers": always avoid any construction using any functions that have range or reference as arguments, like ...xxIFS , OFFSET, ROW etc..
- 6th stage. Sky is the limit. Build our own custom-made lambda helper functions environment. I promise you that if we get here, with only a couple of lambdas we can solve an overly complex spectrum of tasks.
This is my personal opinion based on my experience so far and I wanted to share it. ✌😉
@@excelisfun Why this divagation? Many of us are, or will be, or have been, to stage 3. It is a very important stage in the process, that's why I want to salute any solution that uses lambda helper functions, no matter if they will be ever use in real life or not, if are the best one or not, short or long, eficient or slow. What is important for all of us , is that these solutions can hold clever design tricks or concepts techniques that can be useful in other designes where they will be the Only alternative. My 2 cents. ✌
@@excelisfun PS: I salute all of them minus the ...xxIFS ones. Lol. 🤣
@@Excelambda LOVE^Efficiency * 100000000 : ) : ) : ) : ) : ) But, hey? Why Here? Let's put it as a pinned comment before the LAMBDA video! I will do that now, of if you post it, I will delete mine and pin yours.
So simple yet so powerful. Thanks Mike
Simple is power!!!! Glad you like it all, Matt!!!!
Thanks Mike. I enjoyed that!!!
You are welcome John (Formula Guy)!!!!!!
Super practical explanations, thanks so much!!!
You are welcome so much!!!!
Thanks amazing Mike for this EXCELlent video.
You are welcome amazing Fellow Teacher : ) : )
Thanks, Amazing as always, but please don't forget old-school students :)
below is my suggestion:
=VLOOKUP(S[@Product]&S[@SR],IF({1,0},L[Product]&L[SR],INDEX(L,,MATCH(S[@State],L[#Headers],0))),2,0)
Also for the new school students following formula is my suggestion:
=FILTER(FILTER(L,(L[Product]=S[@Product])*(L[SR]=S[@SR])),COUNTIF(S[@State],L[#Headers]))
Finally, to solve this issue by custom column in my lovely feature (Power Query), the M formula can be this:
=Table.ToList(Table.SelectColumns(Table.SelectRows(IdLookup,(x)=>[Product]=x[Product] and [SR]=x[SR]),[State])){0}
Great 👍
Thanks, Teammate! I Added worksheet formulas to download. I stopped doing M Code lookup formulas a while back, cuz they just always seem so inefficient. But I used to use them a lot : )
Another solution,
=BYROW(S,LAMBDA(r,
XLOOKUP(TRUE,BYROW(L[[Product]:[SR]],
LAMBDA(a,AND(a=TAKE(r,,2)))),
XLOOKUP(DROP(r,,2),D3:F3,L[[Washington]:[California]]))))
Thanks : ) Added to download file.
First to view like and comment 🙏 Unending treasure trove
You get the 1st place trophy, Vijay!!!!
RUclips hero!
@@renegadek9 : )
That was an awesome PQ trick Mike!
Glad you like it, Teammate Chris M!!!!!
Tears of joy, Baam..
Joy, Joy: Tears, Happiness and BAM!!!!
Fabulous, thank you Mike
You are welcome, Salah!!!!
Just another approach for exercise: ⇨ filter (find) Row by composite key; ⇩ select the appropriate Column
=INDEX( FILTER(L, (B10=L[Product])*(C10=L[SR])), XMATCH(D10,L[#Headers]))
More fun with Excel!! Thanks, Victor - I added it to download : )
The same way to solve in Clip with the addition MAP,
=MAP(S[Product],S[SR],S[State],LAMBDA(b,c,d,
XLOOKUP(b&c,L[Product]&L[SR],
XLOOKUP(d,D3:F3,L[[Washington]:[California]]))))
Bam!!! In download : )
One can use this combination too for solving this situation.
=XLOOKUP(
1,
COUNTIFS(S[@Product], L[Product], S[@SR], L[SR]),
XLOOKUP(
S[@State],
L[[#Headers],[Washington]:[California]],
L[[Washington]:[California]]
)
)
Actually, I love this one more. With this one, one doesn't need to use single-row values one by one.
=XLOOKUP(
TRUE,
BYROW(S[@[Product]:[SR]] = L[[Product]:[SR]], LAMBDA(a, AND(a))),
XLOOKUP(
S[@State],
L[[#Headers],[Washington]:[California]],
L[[Washington]:[California]]
)
)
Thanks for your formulas. I added them to the download workbook : ) : )
Very clear explanation.
Glad you like it! Your user name has changed!?!?!? Glad you still got your picture : )
@@excelisfun thanks for pointing it out. I have no idea why it changed. Maybe I can fix it.
@@JonathanExcels RUclips calls them "handles". They were supposed to send all RUclipsrs messages about the impending change. Maybe you did not get it. I think it is easy to change somewhere in settings...
@@excelisfun or…I didn’t read it. Thanks again.
@@JonathanExcels You dialog boxed it lol
Thanks Mike
You are welcome!!!!!
Thank you Sir.. Awesome ..I have a question when I tried to spill the result , It giving the wrong answer apart from the 1st cell.
Instead of using B10&C10 look up value and dragging it down. I've tried B10:B15&C10:C15.
If you want to spill, try this formula:
=INDEX(L,XMATCH(S[Product]&S[SR],L[Product]&L[SR]),XMATCH(S[State],L[#Headers]))
I don't know how to spill directly with XLOOKUP. I did a video on two way lookup spill here: ruclips.net/video/MNMVIIdK40U/видео.html
But the INDEX is really the best.
Awesome as always. Thx Mike :)
You are welcome, Malvin!!!!!
Thank you, sir 💯
You are welcome, cmaman!!!!
Gr8 stuff
Power query tends to unsort the original fact table sort order. Consequently, you have to add an index column to keep the original sorted order. I wonder why you lose the original sort in the fact table?
That is a good question. It happened when I merged and then expanded. I don't know why...
Anyone else know why?
Amazing!!!
Glad you like it, @user-dx8vo1od5i!!!!!
Could you use this as a way to utilize hierarchies? Meaning could you use to populate income statement based on different departments? Or even to pick which level of an income statement to go to ten thousand foot view, next level, next level?
Not sure. THE best place to post detailed questions is mrexcel.com/board
You could also do this the "old-fashioned" way which would work in the new world as is and in the old-world using CTRL+SHIFT+ENTER to commit it...
=INDEX(B$4:F$7,MATCH(B10&C10,B$4:B$7&C$4:C$7,0),MATCH(D10,B$3:F$3,0))
Yes!!! Old School Rules : ) I added it to download workbook file : )
Hello, my friend, there is a problem that I want to solve related to making a hyperlink to data in the same sheet, but I want it inside a drop-down list so that when clicking on any name inside the drop-down list, it goes directly to the location of the cell that contains the same name, for example the cell that I want to go to is located in column AA2 and the drop-down list in column A1 and how I can contact with you if you want the sheet work that I use
I do not know how to do that. Try this great Excel question site: mrexcel.com/board. This board is where I learned a lot of Excel when I started : )
@@excelisfun thanks friend your reply soo great for me
Oh Power Query (B.I.) all this time I kept skipping over power query.
🤦♀
Can't skip over Power Query - it's just too much fun : )
I need to know how to do something in Excel. I have weekly spreadsheets that name staff multiple times each week. So, say Mike is on this week's spreadsheet 3 times. And Susie's name is on the spreadsheet 3 times. And Mike is on last week's spreadsheet 5 times. I want to know how to summarize Mike's name so the total is one for the weeks he is on the list. As well as Susie equaling 1 for the weeks she is on the list. Then I need to add the 52 weeks of spreadsheets names and how many times that person was on a list throughout the year. Again, no matter how many times their name is on a list each week it only counts as 1 time. So if Mike was on the list 42 weeks out of the year how do I easily get that number? Without physically counting them each week. At times I have 35 peoples names on a spreadsheet each week but many of them are on there 2. 3. 4 times. Probably a ridiculous question but I really don't want to physically add up 52 weeks of data by hand. Thanks in advance.
Awesome Mike you are a legend, will take a look at this Power Query solution this morning! Thanks for taking the time to make this video
Just got lucky today with timing. You should enjoy the Power Query! Skate Tough Or Go Home!!!!!!
Hi Sir Mike...cldnt find that Fake Account in my search..Hope YT already removed such ACCOUNT/S.
Regards.
This is great! Even easier than INDEX-MATCH. thanks
Yes!!!! Glad this helps : ) : )