Formula: First non blank after last blank cell in Range

Each row has both blank and no-blank cells, for all you know it could be delivery information where days with no deliveries are blank and delivery days are non-blank. Our task is to return the first delivery after the last non-delivery day in the range
Link to workbook:
docs.google.com/spreadsheets/...
00:00 Introduction and background
01:47 Using XLOOKUP function
02:40 Address function to confirm XLOOKUP
04:10 Circular reference error
05:16 Fixing the circular reference error
06:40 Method 2 -Using XMATCH
08:30 Making the formula spill
09:56 Concluding thoughts

[@GrainneDuggan_Excel]

❤ thanks Victor. Your BYROW explanation is the clearest I have seen.

[@ExcelMoments]

Thanks, Grainne, for the compliment. I am glad it helps you understand.

[@jazzista1967]

Victor.... my mind just exploded this early morning ... I think i have to rewatch the video 10 more times to understand the logic ......hahaha...

[@excelpro365_1]

What a great welcome from GES.

[@ExcelMoments]

Thank you very much

[@IvanCortinas_ES]

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.

[@ExcelMoments]

Thanks Ivan for your kind words

[@stevereed5776]

Thanks Victor.

[@ExcelMoments]

You are welcome, Steve

[@nadermounir8228]

Perfect Video 📹 👌 Thank you Victor

[@ExcelMoments]

You are welcome

[@Softwaretrain]

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),"")

[@ExcelMoments]

Excellent Alternatives. I love all of them.

[@williamarthur4801]

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.

[@ExcelMoments]

Yea, one of the simple tricks that can go unnoticed

[@raymondchew1331]

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

[@excelenthusiasts]

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.

[@ExcelMoments]

Thanks, Ashish, It's always good to hear from you. great formula, once that assumption holds true

[@excelenthusiasts]

@@ExcelMoments Thank you.