Excel: Creating recursive dates in table with calculated values

Question

I have a currency rate table with Date, GBP rate, EUR rate and a (calculated) WEEKDAY values. But the data only runs weekdays.

enter image description here

I'd like to extend the table so that the Date range is continuous (i.e. Mon to Sun) and the GBP and EUR fields are filled such that if it's a Saturday it takes the value from Friday and if it's a Sunday it takes the following Monday value.

To produce:

enter image description here

I've tried XLOOKUP's with IFS but am a bit stumped as to whether I need a lambda function to do this?

Answer 1

Inserting Weekends

• This is a brute-force solution with excessive stacking. Its efficiency may be questionable. Your feedback is welcome and expected.
• Adjust the table name.

=LET(Data,Table1[#All],
    d,DROP(Data,1),mSeq,SEQUENCE(,2,2),
VSTACK(TAKE(Data,1),
    DROP(REDUCE("",SEQUENCE(ROWS(d)),LAMBDA(rr,r,LET(cr,INDEX(d,r),
        IF(INDEX(d,r,4)=5,
            VSTACK(rr,cr,HSTACK(INDEX(cr,,1)+1,INDEX(cr,,mSeq),INDEX(cr,,4)+1)),
        IF(INDEX(d,r,4)=1,
            VSTACK(rr,HSTACK(INDEX(cr,,1)-1,INDEX(cr,,mSeq),INDEX(cr,,4)+6),cr),
        VSTACK(rr,cr)))))),1)))