Unable to get an output

Question

I have 2 excels: One is static file , other has data related to that static file.

I want an output wherein if I change anything in static, it should pick up the same value from data file.

For eg: Currently i want that for file name 5, since static in downside shows column A, it should pick the value from column A in data file that is -7185027.

I fi change the static from A to D, then it should pick data from column D ie 3749677.

Data excel:

COBDATEABCDEFGHFileName411/10/2025    74523   111/10/2025     954123  211/10/2025      -18374299.49 311/10/2025       90006379.6411/10/2025-71850273275473-33059573749677    5

Static excel:

File NameDownsideUpside1E 2 F3G 4 H5AB

DataNath · Accepted Answer

Hey @PM12, how are you expecting the output to look? My initial thoughts are to Transpose the data file and then conduct a Join based on [File Name] and the new [Name] column = [Downside] but I'm not sure what the significance of [Upside] is. Do you want to keep that? Do you want to lose records that don't have a corresponding column letter?

If you want to replace the whole table based on looking up values from the static file then you can do something like this whereby you Transpose both, create a composite key to join on using both the file name and downside/upside, and then Find Replace to add the corresponding value before restoring the table with a Cross-Tab: