Hi

Maybe you simply give us your original setup, and what you want to do with
it. Mostly such complex solutions are needed at all - they are results of
wrong planning at earlier stages.

And when you can't avoid complex formula, you almost always can split it
into several subformulas, and define them as named formulas - thus
shortening your final formula drastically. An example:
MyRange= CHOOSE($A$1,rn1mw,rn2mw,rn3mw,rn4mw,rn5mw)
MyCol=MONTH(TODAY())+1
=VLOOKUP("bongo",MyRange,MyCol,0)


Arvi Laanemets


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
. ..
In my first & second generations of it I did use the CHOOSE() to find the
range I wanted, but I need the formula to be shorter. It is part of a 6
or
7 nested IF formula, that was running up against the character limit for
an
excel formula, not to mention monumentally difficult to debug. the sample
I
used was just a sample, the actual one has a MATCH() as teh 3rd argument
in
the VLOOKUP().

Thanks


"Arvi Laanemets" wrote in message
...
Hi

=VLOOKUP("bongo",CHOOSE(A1,rn1mw,rn2mw,rn3mw,rn4mw ,rn5mw),2,0)

NB! in your original formula, you have 3rd parameter for VLOOKUP equal
to
0.
It is a nonsense - yo are asking to return the value from no column. But
having 4rd parameter equal to 0 has a real meaning.


Arvi Laanemets



"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
Can you us a "variable" in a range name without having to INDIRECT() to
another cell?

-facts-
A1 has a number in it
rn1mw, rn2mw, rn3mw, rn4mw, rn5mw are valid ranges

-problem-
I want to use a vlookup to one of the above ranges. Something like:
VLOOKUP("bongo", CONCATENATE("rn",A1,"mw"), 0)
or
VLOOKUP("bongo", "rn"&A1&"mw", 0)


of course those formulas do not work.....

thanks in advance