get rid of a string in a Vlookup

[JeremyDunn]

Hi,

I have named ranges in a worksheet. eg a few ranges I have named (eg TEST, TestRange1, TestRange2)

I am getting the user to select via a drop down box which range they want to do a vlookup on. Say this is in Cell A1.

In a different cell I then have a vlookup formula referencing the value of the dropdown box. eg vlookup("Hello",A1,2,False) The vlookup does not work because it looks at the value in A1 (eg TestRange1) as a string ie "TestRange1"


Does anyone know how to get around this. ANy help you could provide would be much appreciated..

Thanks
Jeremy

 

[onlyadrafter]

Hello,

Look at INDIRECT

 

[Richard Schollar]

Hi Jeremy

Use INDIRECT:

=VLOOKUP("Hello",INDIRECT(A1),2,False)

Best regards

Richard

 

[JeremyDunn]

Hi Richard / onlyadrafter,

Thanks very much for that.. I knew there was a way.. Saved me another hour of mucking around..

Cheers
Jeremy