Reference Table lookup with Column and Row Headers

[KP_dup_173]

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

[Luke_C]

Hi @KP_dup_173  could you post the data instead of screenshots? Will be easier to look at/play around with. 

[Jonny]

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

[KP_dup_173]

Thank you Luke-C - noted for next time 🙂

[KP_dup_173]

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