Replicate XLOOKUP if not found return smaller item

[JLiou]

Hi community,

I am trying to replicate Excel's Xlookup's if not found return next smaller item in the sense that I have in Table 1 dates and return value and in Table 2 a list of Week Starting.  What I want in Table 3 is to return the Value column from Table 1 based on a similar method to Excel XLOOKUP's "Exact match. If none found, return the next smaller item".  See attached.  I've trying linking Table 1 and 2 together and compare date which resulted in a very large table - is there any more efficient way of getting the results in Table 3? 

[Matt_D · Accepted solution]

Hi @JLiou 

One way to do this is by generating a value for each date in TABLE 1, you then have a like for like join on your Week Start in TABLE 2, where no join value is 0 (zero).

I set the last date for 365 days into the future in the Multi-Row Formula tool to account for any future Week Start dates. You can just adjust accordingly.

[JLiou]

Thanks @Matt_D .  I was afraid that I had to create extra rows/dates in Table1 but I think this is the best solution and is unavoidable.