# Dynamic formula variables [closed] I'm trying to generate a formula in libre that would select a column based on drop down list selection. I realize this is invalid but I think it illustrates what I'm trying to do better than my words could.

=IF($A$2=1:52,C5:BA5,)-(IF($B$2=1:52,C5:BA5,)

Which seemed to me like a logical simplification of this ridiculously long formula that I believe has too many IF's to work. =IF($A$2=1,C5,(IF($A$2=2,D5,(IF($A$2=3,E5,(IF($A$2=4,F5,(IF($A$2=5,G5,(IF($A$2=6,H5,(IF($A$2=7,I5,(IF($A$2=8,J5,(IF($A$2=9,K5,(IF($A$2=10,L5,(IF($A$2=11,M5,(IF($A$2=12,N5,(IF($A$2=13,O5,(IF($A$2=14,P5,(IF($A$2=15,Q5,(IF($A$2=16,R5,(IF($A$2=17,S5,(IF($A$2=18,T5,(IF($A$2=19,U5,(IF($A$2=20,V5,(IF($A$2=21,W5,(IF($A$2=22,X5,(IF($A$2=23,Y5,(IF($A$2=24,Z5,(IF($A$2=25,AA5,(IF($A$2=26,AB5,(IF($A$2=27,AC5,(IF($A$2=28,AD5,(IF($A$2=29,AE5,(IF($A$2=30,AF5,(IF($A$2=31,AG5,(IF($A$2=32,AH5,(IF($A$2=33,AI5,(IF($A$2=34,AJ5,(IF($A$2=35,AK5(IF($A$2=36,AL5,(IF($A$2=37,AM5,(IF($A$2=38,AN5,(IF($A$2=39,AO5,(IF($A$2=40,AP5,(IF($A$2=41,AQ5,(IF($A$2=42,AR5,(IF($A$2=43,AS5,(IF($A$2=44,AT5,(IF($A$2=45,AU5,(IF($A$2=46,AV5,(IF($A$2=47,AW5,(IF($A$2=48,AX5,(IF($A$2=49,AY5,(IF($A$2=50,AZ5,(IF($A$2=51,BA5,(IF($A$2=52,BB5,))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

A2 and B2 are drop downs 1-52 which I would like to have the formula insert the cell value from that column/row.

Any help would be appreciated; tried to post a screen shot of the table but alas I lack the karma to upload.

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-10-26 22:07:11.690457

The index= answer below does appear to work for what I was attempting to do/describe above. Many thanks.

Sort by » oldest newest most voted I think you are looking for extract the value in a position inside a range:

=OFFSET(C5;0;$A$2-1)


or

=INDEX(C5:BA5;0;$A$2)

more

+1! The first parameter of the For OFFSET() can be B5: so then don't have to subtract 1 from the $A$2 =OFFSET(B5;0;$A$2)