Vlookup - dynamic range reference?

[aseanor]

I am trying to do a vlookup that will look in a different range based on
an input cell.

=vlookup(d1,b1,2,false) where b1 changes to different sheets (i.e. if
b1 = adam, then look in named range adam).

would also like to do it without using named ranges but seem to be
stuck. I tried using indirect(b1) but that didn't work.

thanks in advance

Adam

 

[Dave Peterson]

Why didn't this work?
=vlookup(d1,indirect(b1),2,false)

Are you sure you had a named range for the value in B1?

Did it consist of at least 2 columns?

 

[aseanor]

I made the named range myself.

I was able to get the formula to work if I used indirect to concatenate
the actual range address from the sheet name and the cell references but
if I used indirect(c2) where c2 = range name, I get an answer but the
wrong one.


named range: bmo
28
sheet1 =B4&"!" c424 <= using cells to assemble range reference
=VLOOKUP(D3,INDIRECT(C2),2) {doesn't work, gives wrong answer)
=VLOOKUP(D3,INDIRECT($C4&D4),2,FALSE) {works, but would rather use
name}

=VLOOKUP(D3,bmo,2,FALSE) {this was my check, it worked fine}

thanks for your help.

Adam

 

[Dave Peterson]

I'm sorry.

I don't understand the explanation.

I made the named range myself.

I was able to get the formula to work if I used indirect to concatenate
the actual range address from the sheet name and the cell references but
if I used indirect(c2) where c2 = range name, I get an answer but the
wrong one.


named range: bmo
28
sheet1 =B4&"!" c424 <= using cells to assemble range reference
=VLOOKUP(D3,INDIRECT(C2),2) {doesn't work, gives wrong answer)
=VLOOKUP(D3,INDIRECT($C4&D4),2,FALSE) {works, but would rather use
name}

=VLOOKUP(D3,bmo,2,FALSE) {this was my check, it worked fine}

thanks for your help.

Adam

 

[aseanor]

I was afraid of that. I have attached a zip of the sample file. Maybe
that will help.

Thanks again

Adam

I'm sorry.

I don't understand the explanation.

http://www.excelforum.com/showthread.php?threadid=572769

+-------------------------------------------------------------------+
|Filename: vlookup.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5225 |
+-------------------------------------------------------------------+

 

[Debra Dalgleish]

Maybe this version:

=VLOOKUP(D3,INDIRECT(C2),2) {doesn't work, gives wrong answer)

give the incorrect answer because you didn't include FALSE as the last
argument.

 

[Dave Peterson]

There are lots of people who won't open attachments. (I don't.) There are lots
of people who don't connect to the newsgroups through excelforum so they can't
open the attachment even if they wanted to.

You're going to limit the number of responses by including attachments.

If you try explaining your problem in plain text, you may get some answers.