Reference cell with a name range in VLOOKUP

Question

I'm trying to do a VLOOKUP, but for the table array, I'm trying to reference a single cell that holds the name range value. Is there a way to make the VLOOKUP recognise the name range without using INDIRECT?

The addon I'm using to convert the Excel doc to a HTML interface, doesn't allow for INDIRECT unfortunately. VBA won't work as well.

Any help would be greatly appreciated.

Unknown Unknown · Accepted Answer · 2015-08-20T01:12:06

If the name of the range (e.g. MyRange) is in A1 then something like this,

with activesheet
    application.vlookup(123, range(.Range("A1").value), 2, false)
    application.vlookup(123, range(.Cells(1, 1).value), 2, false)
end with

Note that it is important that the parent worksheet of the cell containing the name of the named range be specified. Not so much for the Range object actually getting the range with the string returned.

Reference cell with a name range in VLOOKUP

1 Answers