Search in cell defined by formula

[Nbisgaard]

Hello there,

i got a relatively simple issue but i can't quite figure out how to solve it.

I am currently using the formula:

=INDEX('EX22'!$M:$M;MATCH($A$2;'EX22'!$A:$A;0)+1)

Basically, what it does is that it looks in a big Data sheet called EX22. It looks for the value i defined in cell A2. Afterwards it finds me the value in the same row in column M:M, and give me the value one row down (last part, +1).

What i want to do is that the 'EX22'!$M:$M part can somehow be written as a "formula". I don't always want to look in M:M. The location which i want to retrieve the value in depends on another parameter
where i know the column number (M being column 13, going from column 5 (E), to column 20 (T).

Instead of manually writing: 'EX22'!$M:$M in the formula i want to write 'EX22'!$char(A3+64):$char(A3+64)
Where A3 would be the cell i define the column in.
I know that the above example wouldn't work as excel doesn't really work that way.

So my question is:
Does anyone have a suggestion as to how this could be done?

Any help is greatly appreciated.

[Special-K]

You'r pretty much there, you need to use INDIRECT()
Try (untested)

=INDEX(INDIRECT("'EX22'!"&CHAR(A3+64)&":"&CHAR(A3+64));MATCH($A$2;'EX22'!$A:$A;0)+1)

NOTE: Be careful if your columns go past 26, your calculation will not return column 27 as AA

[Nbisgaard]

Bingo!
I've never actually used the indirect formula before but i suspect i ll be using it more in the future.

Thanks alot for the help.