Vlookup - dynamic range reference?
 

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


--
aseanor
------------------------------------------------------------------------
aseanor's Profile: http://www.excelforum.com/member.php...o&userid=29161
View this thread: http://www.excelforum.com/showthread...hreadid=572769

Vlookup - dynamic range reference?
 

don't want to use nested ifs as there are about 12 possible ranges


--
aseanor
------------------------------------------------------------------------
aseanor's Profile: http://www.excelforum.com/member.php...o&userid=29161
View this thread: http://www.excelforum.com/showthread...hreadid=572769

Vlookup - dynamic range reference?
 

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 wrote:

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

--
aseanor
------------------------------------------------------------------------
aseanor's Profile: http://www.excelforum.com/member.php...o&userid=29161
View this thread: http://www.excelforum.com/showthread...hreadid=572769

--

Dave Peterson

Vlookup - dynamic range reference?
 

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&"!" c4:p24 <= 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
------------------------------------------------------------------------
aseanor's Profile: http://www.excelforum.com/member.php...o&userid=29161
View this thread: http://www.excelforum.com/showthread...hreadid=572769

Vlookup - dynamic range reference?
 

I'm sorry.

I don't understand the explanation.

aseanor wrote:

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&"!" c4:p24 <= 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
------------------------------------------------------------------------
aseanor's Profile: http://www.excelforum.com/member.php...o&userid=29161
View this thread: http://www.excelforum.com/showthread...hreadid=572769

--

Dave Peterson

Vlookup - dynamic range reference?
 

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

Thanks again

Adam

Dave Peterson Wrote:
I'm sorry.

I don't understand the explanation.

aseanor wrote:

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&"!" c4:p24 <= 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

------------------------------------------------------------------------
aseanor's Profile:
http://www.excelforum.com/member.php...o&userid=29161
View this thread:
http://www.excelforum.com/showthread...hreadid=572769

--

Dave Peterson


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

--
aseanor
------------------------------------------------------------------------
aseanor's Profile: http://www.excelforum.com/member.php...o&userid=29161
View this thread: http://www.excelforum.com/showthread...hreadid=572769

Vlookup - dynamic range reference?
 

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.

aseanor wrote:
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&"!" c4:p24 <= 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




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Vlookup - dynamic range reference?
 

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.

aseanor wrote:

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

Thanks again

Adam

Dave Peterson Wrote:
I'm sorry.

I don't understand the explanation.

aseanor wrote:

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&"!" c4:p24 <= 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

------------------------------------------------------------------------
aseanor's Profile:
http://www.excelforum.com/member.php...o&userid=29161
View this thread:
http://www.excelforum.com/showthread...hreadid=572769

--

Dave Peterson

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

--
aseanor
------------------------------------------------------------------------
aseanor's Profile: http://www.excelforum.com/member.php...o&userid=29161
View this thread: http://www.excelforum.com/showthread...hreadid=572769

--

Dave Peterson