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?
Solved! Go to 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.
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.