Text Functions and setting up a dynamic VLookup

[Yellowfin]

Good morning,

I'm struggling with a vlookup() that I want to make a bit more dynamic.

I have a series of named ranges that I'd like to feed into the Table Array field (the 2nd vlookup parameter). The names of the range are all of the form Rates_X where "X" can be 1,2,3, or 4. The X value appropriate for the row where I'm placing the lookup can be determined easily. So I tried to create a new cell that was ="Rate_"&X and then using a T() function with that cell reference as the Table Array. No luck. I get a #Value error which indicates it is not intrpretting the text as a name.

Any idea how I can get the function to recognize the input as a name?

Thanks in advance.


Jim

 

[Aladin Akyurek]

=VLOOKUP(lookup-value,INDIRECT("Rate_"&X),col-index,match-type)

where X is a value that completes the name of the range of interest.
This message was edited by aladin akyurek on 2002-09-08 03:31

 

[lenze]

Here is an example using Aladin's Formula

Book1
	A	B	C	D	E	F	G	H	I
1	Type		Tbl Num		Table Rate_1			Table Rate_2
2	Circle		2		Type	Style		Type	Style
3		Red			Circle	Red		Circle	Green
4		Green			Square	Blue		Square	Yellow
...

 

[Yellowfin]

Thank You!

I knew there was another function involved, I just couldn't figure out that it was Indirect().

As always, thanks.



Jim