using a variable range name for the vlookup array definition

[daviddillon]

I'm trying to use a range name as the "array" within a vlookup formula. I can type in a range name and it works, but I want the array to be a variable range name based on the result of another cell.

I've spent too much time on this already...any ideas?

 

[Norie]

David

Without further info all I can suggest is INDIRECT.

=VLOOKUP(A2, INDIRECT(B1), 3, 0)

Where B1 has the named range.

 

[daviddillon]

Thanks...that works, but I had to use "TRUE" instead of "0" as the range_lookup value.

Thank you so much!

David

 

[Norie]

David

You only use TRUE in a VLOOKUP if you are looking for the closest match.

Is that what you're doing?

 

[daviddillon]

Yes...I'm looking for the closest match within a rate table that looks like this:

Low High Rate
1 10 $10
11 20 $20
21 30 $30
etc......

So if 11 or 19 is the lookup value, $20 is the returned value from the lookup function.