I start watching a new video of Victor and I think... (there's no way he'll surprise me again). Well, he surprises me twice. Highly recommended. Thank you.
Thanks Sir! Could I know whether we can use CHOOSEROWS instead of FILTER function? CHOOSECOLS is used in this video to extract the columns we want. This makes me think CHOOSEROWS to replace FILTER
I slightly misunderstood, and thought I was supposed to get the last available for 'Jumper cables', I used Indirect inside offset, LET( o, OFFSET( INDIRECT( CELL("address", XLOOKUP(TRUE,ISBLANK(F4:O4),F4:O4,,0,-1))),0,1), p, XLOOKUP(FALSE,ISBLANK(F4:O4),F4:O4,,0,-1), IF( O4="",p,o)) One thing, I did not know that was hwo you looked up blank in xmatch,, just skip, I have tried using using "" of course got NA and just gave up, so thanks for that.
Great challenge Victor, I came up with other solutions as below: =IFERROR(INDEX(B5:K5,XMATCH(,B5:K5,,-1)+1),"") =IFERROR(INDEX(B5:K5,MATCH(2,IF(B5:K5="",1,""))+1),"") =IFERROR(INDEX(B5:K5,MAX((B5:K5="")*SEQUENCE(,COLUMNS(B5:K5)))+1),"") =IFERROR(TAKE(DROP(XLOOKUP(,B5:K5,B5:K5,,,-1):K5,,1),,1),"") =LOOKUP(2,HSTACK(1,IF(B5:K5="",1,"")),B5:K5) =IFERROR(INDEX(B5:K5,MAX((B5:K5="")*COLUMN(B5:K5))),"") =IFERROR(CHOOSECOLS(B5:K5,MAX((B5:K5="")*COLUMN(B5:K5))),"") =IFERROR(INDEX(B5:K5,XMATCH(TRUE,LEN(B5:K5)=0,,-1)+1),"")
Hi, Assuming the dates in row 4 are consecutive, this formula works as well =XLOOKUP(XLOOKUP(TRUE,B5:K5="",$B$4:$K$4,,,-1)+1,$B$4:$K$4,B5:K5,"") Hope this helps.
❤ thanks Victor. Your BYROW explanation is the clearest I have seen.
Thanks, Grainne, for the compliment. I am glad it helps you understand.
I start watching a new video of Victor and I think... (there's no way he'll surprise me again).
Well, he surprises me twice. Highly recommended. Thank you.
Thanks Ivan for your kind words
Victor.... my mind just exploded this early morning ... I think i have to rewatch the video 10 more times to understand the logic ......hahaha...
What a great welcome from GES.
Thank you very much
Thanks Victor.
You are welcome, Steve
Perfect Video 📹 👌 Thank you Victor
You are welcome
Thanks Sir! Could I know whether we can use CHOOSEROWS instead of FILTER function? CHOOSECOLS is used in this video to extract the columns we want. This makes me think CHOOSEROWS to replace FILTER
I slightly misunderstood, and thought I was supposed to get the last available for 'Jumper cables', I used
Indirect inside offset,
LET( o, OFFSET( INDIRECT( CELL("address", XLOOKUP(TRUE,ISBLANK(F4:O4),F4:O4,,0,-1))),0,1),
p, XLOOKUP(FALSE,ISBLANK(F4:O4),F4:O4,,0,-1), IF( O4="",p,o))
One thing, I did not know that was hwo you looked up blank in xmatch,, just skip, I have tried using
using "" of course got NA and just gave up, so thanks for that.
Yea, one of the simple tricks that can go unnoticed
How can I get the result as the dates on which there is start of the number
Great challenge Victor, I came up with other solutions as below:
=IFERROR(INDEX(B5:K5,XMATCH(,B5:K5,,-1)+1),"")
=IFERROR(INDEX(B5:K5,MATCH(2,IF(B5:K5="",1,""))+1),"")
=IFERROR(INDEX(B5:K5,MAX((B5:K5="")*SEQUENCE(,COLUMNS(B5:K5)))+1),"")
=IFERROR(TAKE(DROP(XLOOKUP(,B5:K5,B5:K5,,,-1):K5,,1),,1),"")
=LOOKUP(2,HSTACK(1,IF(B5:K5="",1,"")),B5:K5)
=IFERROR(INDEX(B5:K5,MAX((B5:K5="")*COLUMN(B5:K5))),"")
=IFERROR(CHOOSECOLS(B5:K5,MAX((B5:K5="")*COLUMN(B5:K5))),"")
=IFERROR(INDEX(B5:K5,XMATCH(TRUE,LEN(B5:K5)=0,,-1)+1),"")
Excellent Alternatives. I love all of them.
I want my results to be the month in which the first order was delivered... anyone can hep with this formula please..
You could use say an XLOOKUP like this for row 5 =XLOOKUP(TRUE,B5:K50,$B$4:$K$4), where your dates are in B4 to K4
@@ExcelMoments Thank you so much.
Hi,
Assuming the dates in row 4 are consecutive, this formula works as well
=XLOOKUP(XLOOKUP(TRUE,B5:K5="",$B$4:$K$4,,,-1)+1,$B$4:$K$4,B5:K5,"")
Hope this helps.
Thanks, Ashish, It's always good to hear from you. great formula, once that assumption holds true
@@ExcelMoments Thank you.