View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.misc
Debra Dalgleish Debra Dalgleish is offline
external usenet poster
 
Posts: 2,979
Default 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