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