Reference Table lookup with Column and Row Headers

Question

Hi All,

Wondered if anyone can assist with a lookup table. It needs to check for a ccy holiday and update for the next day after holiday or weekend. I am thinking of something like a ref data table with holidays & weekends populated. Then my other input file would have various CCYs and a date - but I am not able to figure out how to do a two way look up i.e 1. Find CCY, 2. Check if date is available. If not populate with next available date. Perhaps there is an easier way, looping back until it finds one available?For example if the date for my JPY is 08/01/21 it would see that is not available, nor the next 4 and would return the valid day 13/01/21

KP_dup_173 · Answer

Thanks Jonny - This looks great. I need to have a play to build in to my flow. Appreciate your help.

KP_dup_173 · Answer

Thank you Luke-C - noted for next time 🙂

Jonny · Answer

Hi @KP_dup_173 ,

I tried my hand with some sample data created, next time it would be easier with a sample workflow :P.

The trick is how your 1st table data is structured, you cant read the data in the Column headers so I had to transpose.

From this:

To this:

then join at CCY level, then this formula:

and summarise tool:

et voila:

Probably you have to bullet proof it with some more scenarios, but it seems to have worked!

Hope that helps!

cheers!

Jonny

Reference Table lookup.yxmd