Replicate XLOOKUP if not found return smaller item

Question

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?

Replicate Excel Xlookup.xlsx

Matt_D · Accepted Answer

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.