Vlookup using named ranges

[JasonSGRankin]

I am looking to use a Vlookup function with a named range. The problem is that users have the option of selecting a "letter" eg W from a validation list. Once a letter is selected it determines, via a lookup the name of the "Named Range", ie EG;

If the user selects W, then the named range will be "ACT1". the value "ACT1" will be placed in a destination cell. The vlookup function will then lookup a value in the selected named range being ACT1. Therefore is the destination cell that the "ACT1" was placed in was [D1], then the formula would read as:

VLOOKUP(lookup_value,[D1],4,FALSE)

The result I get is #N/A. I don't know how to get the formula to realise that it needs to lookup the value in the range specified by the name in cell [D1]. Is there a solution to this?

Please also not that the named range "ACT1" is on a seperate worksheet tab within the same workbook.

 

[Andrew Poulsom]

Try:

=VLOOKUP(lookup_value,INDIRECT(D1),4,FALSE)

 

[Northerner]

=VLOOKUP(lookup_value,INDIRECT(D1),4,FALSE)

With D1 being where your ACT1/ACT2 is displayed.

 

[Aladin Akyurek]

=VLOOKUP(lookup_value,IF(dropdown_cell="W",ACT1,ACT2),4,0)

if you have just 2 choices.

 

[JasonSGRankin]

Thank You very much, the INDIRECT() function worked perfectly. Thanks for taking time out to assist me.