how do i use the text in a cell as a named range in a vlookup form

[SB_2009]

I have a list of data that is refreshed from a system and i wish to use the
text returned in each cell as the named range reference in a vlookup so that
i can fill down the formula against the full list.
For example:
Cell Text in cell Deesired Formula
A1 Yellow = vlookup(c1,Yellow,2,false)
A2 Red = vlookup(c2,Red,2,false)
A3 Blue = vlookup(c2,Blue,2,false)
But using the text in the cell as the named range e.g =vlookup(c1,a1,2,false)

Is there anyway to do this?

 

[Gary''s Student]

Say we have a table from I2 thru J4:

1 rose
2 bud
3 tree

and we assign the Name plant to the table.

say we enter 2 in A1 and plant in A2.

=VLOOKUP(A1,I2:J4,2)
=VLOOKUP(A1,plant,2)
=VLOOKUP(A1,INDIRECT(A2),2)

will all work and all return bud.

So as long as the proper Table Name gets in A2 you can use it in the VLOOKUP()

 

[SB_2009]

Brilliant - that works exactly as i wanted.

Cheers!