Cool Victor! Here's another one that will work: =IFERROR(INDEX(B3:H3,,LARGE(IF(ISNUMBER(B3:H3),COLUMN(B3:H3)-COLUMN(B3)+1),$I$1)),""). And here is one that will do it all in one cell: =BYROW(B3:H8,LAMBDA(x,IFERROR(INDEX(FILTER(x,x""),IF(COUNT(FILTER(x,x""))-$I$1+1>0,COUNT(FILTER(x,x""))-$I$1+1,"")),""))). Fun to solve in different ways! Thanks and thumbs up!!
These days when I am putting up a video, i am already saying to myself, *Wayne has something cool coming* I like the COLUMN approach, typically, how I solved it Pre-M365. If one uses an INDEX from A3:H3, instead of B3:H3, one can shorten the formula to =IFERROR(INDEX(A3:H3,,LARGE(IF(ISNUMBER(B3:H3),COLUMN(B3:H3)),$I$1)),""), not necessarily more efficient, just shorter 😁😁. The BYROW builds off the FILTER approach and that's really cool for a 1-formula solve all approach. Thanks for sharing. Always many ways to skin a cat
@@ExcelMoments Haha :)) Thanks Victor. I think it is easier to be in the audience, as your videos spark the inspiration to build off what you have presented. I always try to solve first a few ways before watching.. best way to learn is with hands on the keyboard! But then by watching your solutions, I learn even more. Sharing makes us all better. Thanks for all your great videos and ideas. Always look forward to what you have in store next!. Cheers!!
if you mean, 2nd non-blank cell from the left, then you can use a construct like this =INDEX(FILTER(B3:I3,B3:I3""),,2), which simply means filter out the blanks, and then use INDEX to pick the 2nd element, if I got your question correctly
@@ExcelMoments yes you got it - trying to grab the left most, and second left most populated cells. I'm running into issues because it wont pick up the "empty" cells as empty or zeros because of the formulas, so trying to solve for that if you have any advice! Appreciate the quick response and help!
@user-vv7xg1tg9t so the empty cells there are not truly empty, they are results of a calculation is that the cade? Like writing a formula with iferror that returns blank, but yes that would not be truly blank
You may want to check the formula again though. Your formula finds the last non-blank cell, the video is about finding the second to the last or third to last, like a dynamic Last N non blank cell in addition, your formula as written would work when B3:H3 contains Texts, not numbers like I have it. You can test again and revert in case i misunderstood your formula
@@ubaidillahmuhammad20 It can be used to solve the problem in this video, but i guess your request is beyond this video. Any [articular application of this combo in mind?
LOVE YOUR VIDEO YOU ARE BRILLIANT MAN, THANK YOU FOR EVERYTHING!
Thank you for the kind words. You are welcome
Cool Victor! Here's another one that will work: =IFERROR(INDEX(B3:H3,,LARGE(IF(ISNUMBER(B3:H3),COLUMN(B3:H3)-COLUMN(B3)+1),$I$1)),""). And here is one that will do it all in one cell: =BYROW(B3:H8,LAMBDA(x,IFERROR(INDEX(FILTER(x,x""),IF(COUNT(FILTER(x,x""))-$I$1+1>0,COUNT(FILTER(x,x""))-$I$1+1,"")),""))). Fun to solve in different ways! Thanks and thumbs up!!
These days when I am putting up a video, i am already saying to myself, *Wayne has something cool coming*
I like the COLUMN approach, typically, how I solved it Pre-M365. If one uses an INDEX from A3:H3, instead of B3:H3, one can shorten the formula to =IFERROR(INDEX(A3:H3,,LARGE(IF(ISNUMBER(B3:H3),COLUMN(B3:H3)),$I$1)),""), not necessarily more efficient, just shorter 😁😁. The BYROW builds off the FILTER approach and that's really cool for a 1-formula solve all approach. Thanks for sharing. Always many ways to skin a cat
@@ExcelMoments Haha :)) Thanks Victor. I think it is easier to be in the audience, as your videos spark the inspiration to build off what you have presented. I always try to solve first a few ways before watching.. best way to learn is with hands on the keyboard! But then by watching your solutions, I learn even more. Sharing makes us all better. Thanks for all your great videos and ideas. Always look forward to what you have in store next!. Cheers!!
Really helpful, Thank you!
You are welcome
Thank you!
You are welcome
Perfect 👍
Thanks for the feedback
Very Good Video, Thank you sir!
Glad it was helpful!
Thanks so much Victor! How would I adjust the formula in order to make it choose the left most/second left most cell, instead of the right ones?
if you mean, 2nd non-blank cell from the left, then you can use a construct like this =INDEX(FILTER(B3:I3,B3:I3""),,2), which simply means filter out the blanks, and then use INDEX to pick the 2nd element, if I got your question correctly
@@ExcelMoments yes you got it - trying to grab the left most, and second left most populated cells. I'm running into issues because it wont pick up the "empty" cells as empty or zeros because of the formulas, so trying to solve for that if you have any advice!
Appreciate the quick response and help!
@user-vv7xg1tg9t so the empty cells there are not truly empty, they are results of a calculation is that the cade? Like writing a formula with iferror that returns blank, but yes that would not be truly blank
No more advice needed, I've got it! Just did an 'IF' with 0 being "". Thanks again for your help!
@@SalSalemme Great. Well done
This function may not have been available we you recorded this: =XLOOKUP("*",B3:H3,B3:H3,0,2,-1). A little simpler...
You may want to check the formula again though. Your formula finds the last non-blank cell, the video is about finding the second to the last or third to last, like a dynamic Last N non blank cell
in addition, your formula as written would work when B3:H3 contains Texts, not numbers like I have it. You can test again and revert in case i misunderstood your formula
Perfect 👍
Thank you. Much appreciated
make video combination Index and Aggregate sir ...
@@ubaidillahmuhammad20 It can be used to solve the problem in this video, but i guess your request is beyond this video. Any [articular application of this combo in mind?