Dynamic vlookup of columns based on dates

[Sohdaw]

Hi,

I have two sets of data: 

Table A = Formula to provide Dates 

Description	T-1	T	T+1	T+2	T+3
Description	Date T-1 eg. 02-Jan-2023	Date T	Date T+1	Date T+2	Date T+3

Table B - note dates change daily, starting with T-1 

Description	02-Jan-2023	03-Jan-2023	04-Jan-2023	05-Jan-2023	06-Jan-2023	07-Jan-2023	08-Jan-2023	09-Jan-2023	10-Jan-2023
XXX	123	123	123	123	123	123	123	123	123
YYY	125	125	125	125	125	125	125	125	125
CCC	345	345	345	345	345	345	345	345	345

How do i do a dynamic vlookup between Table A and Table B to only pick up the columns based on Table A?

I am unable to use a JOIN tool since LEFT Join can be T-1 but RIGHT Join will be the date eg. 02-Jan-2023 which changes daily, and i'll need to have 5 Joins. 

Thanks

[Adrian_T]

Hi @Sohdaw, here's one way of doing it. The idea is to transpose your values such that dates in Table B fall into 1 single column, which serves as the key column for your join. Depending on the desired output format, we can then use a Cross Tab tool to bring the dates back as column headers.

Sample workflow is attached as well. Hope this helps! 🙂