using a variable range name for the vlookup array definition

daviddillon

New Member
Joined
Nov 29, 2005
Messages
3
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?
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
David

Without further info all I can suggest is INDIRECT.

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

Where B1 has the named range.
 

daviddillon

New Member
Joined
Nov 29, 2005
Messages
3
Thanks...that works, but I had to use "TRUE" instead of "0" as the range_lookup value.

Thank you so much!

David
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
David

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

Is that what you're doing?
 

daviddillon

New Member
Joined
Nov 29, 2005
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,820
Messages
5,574,509
Members
412,599
Latest member
Schu94
Top