Named Range references in function formulas

[Bob]

I have a spreadsheet in which I have a series of named ranges that each refer
to a series of cells on a given row of the spreadsheet. For instance
row101=Development!$C$2:$M$2,row102=Development!$C$3:$M$3, etc.

I am interested in using these "Names" in "Match" and "Lookup" functions.
They work fine when I type the names in the functions or when I select the
corresponding ranges. However what I was really hoping to be able to do was
to have a user enter the numeric part of the "Name" in a cell (i.e. 101) and
somehow be able to use this value to "generate" the "Name" and then use that
in the "Match" & "Lookup" functions. I can "generate the "Name" using the
concatenate function but it doesn't work in the "Match" and "Lookup"
functions - they result in "# value" errors.

I am convinced there is something simple that I am missing but can't figure
out how to get this to work. It may be that this approach won't work at all,
if this is the case any suggestions on how I might accomplish this type of
lookup based on a user input value would be appreciated.

 

[RagDyeR]

Use the Indirect() function.

For example:
rng1 = A1 to B10
rng2 = A11 to B20

Range number (either 1 or 2) entered into C1.

=VLOOKUP(25,INDIRECT("rng"&C1),2,0)


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I have a spreadsheet in which I have a series of named ranges that each
refer
to a series of cells on a given row of the spreadsheet. For instance
row101=Development!$C$2:$M$2,row102=Development!$C$3:$M$3, etc.

I am interested in using these "Names" in "Match" and "Lookup" functions.
They work fine when I type the names in the functions or when I select the
corresponding ranges. However what I was really hoping to be able to do was
to have a user enter the numeric part of the "Name" in a cell (i.e. 101) and
somehow be able to use this value to "generate" the "Name" and then use that
in the "Match" & "Lookup" functions. I can "generate the "Name" using the
concatenate function but it doesn't work in the "Match" and "Lookup"
functions - they result in "# value" errors.

I am convinced there is something simple that I am missing but can't figure
out how to get this to work. It may be that this approach won't work at
all,
if this is the case any suggestions on how I might accomplish this type of
lookup based on a user input value would be appreciated.