Using the text from a cell as a range name in a formula

[Fletch]

I would like to create a forumla that uses the text from column A to be the
source for the named range in my forumla in column B.

For example, I have a spreadsheet where coulmn A has rows each with the name
of a city (branch location). I have a seperate table of data that has named
ranges (named by city). Can I write a LOOKUP formula that uses the text in
column A to be the source of the named range inside the formula. Referring
to the text in column A would save me the effort of typing the named range in
each formula

FYI - I don't think A pivot table would work for creating this spreadsheet
because the data is exported from another application and isn't in a friendly
format.

 

[Bob Umlas, Excel MVP]

=Lookup(indirect(A1),...)
or
=LOOKUP(E3,INDIRECT(A1),...)
etc.

Indirect function treats text as references.
Bob Umlas

 

[Fletch]

Thanks!

=Lookup(indirect(A1),...)
or
=LOOKUP(E3,INDIRECT(A1),...)
etc.

Indirect function treats text as references.
Bob Umlas

 

[Biff]

Hi!

Try this:

=VLOOKUP(lookup_value,INDIRECT(A2),2,0)

Where A2 = city_name and city_name is the named range.

Biff