MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Changing range name in functions


Posted by Brian Rogers on October 31, 2001 1:16 PM

I want to vary the range the VLOOKUP function uses. So I created the formula ....

VLOOKUP(lookup_value,CELL("contents",VARYNAME),2,FALSE)

where VARYNAME is a cell which contains the Range Name I want the function to use. But I get the error #VALUE!

Is there any way to vary the range the VLOOKUP function uses??


Posted by Aladin Akyurek on October 31, 2001 1:23 PM

Put the range of interest in a cell of its own, say in E1, and change your VLOOKUP formula to:

VLOOKUP(lookup_value,INDIRECT(E1),2,FALSE)

Aladin

========

Posted by brian rogers on November 01, 2001 1:08 PM

Aladin,

your INDIRECT solution worked perfectly. Thanks very much.