Unable to get an output

[PM12]

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:

COBDATE	A	B	C	D	E	F	G	H	FileName4
11/10/2025					74523				1
11/10/2025						954123			2
11/10/2025							-18374299.49		3
11/10/2025								90006379.6	4
11/10/2025	-7185027	3275473	-3305957	3749677					5

Static excel:

File Name	Downside	Upside
1	E
2		F
3	G
4		H
5	A	B

[DataNath · Accepted solution]

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:

[PM12]

Thanks a lot. it helped.