Using Named Cell content value in VLookup formula

[lanman]

Hi. I am trying to reference a named cell in a VLOOKUP formula as the table_array parameter.

The Named Cell "Input1" contains a table array definition of '[Test.xls]Sheet1'!$H$10:$J$100

When I try VLOOKUP($D3,Input1,3,0) - This returns #N/A as a result.
When I try VLOOKUP($D3,INDIRECT(Input1),3,0) - This returns a #REF error.
When I copy and past the contents of the Input1 named cell directly into the VLOOKUP formula, it works so I know the array reference is valid.

I am not proficient in VBA so I would like to accomplish this using basic Excel formulas if possible. Does anyone have an idea how I can reference the named cell and have the cell value returned in the VLOOKUP function?

Thanks

 

[alfranco17]

I split Input1 into

Input1:

[test.xlsx]Sheet1

<tbody>
</tbody>

and
Input2:

!$H$10:$J$100

<tbody>
</tbody>

Then used this formula: =VLOOKUP($d3,INDIRECT("'" & input1 & "'" & input2),3,0)

Barry Houdini posted the right syntax here:

You need a syntax like this
<code style="margin: 0px; padding: 1px 5px; border: 0px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">=VLOOKUP(A2,INDIRECT("'"&A1&"'!$A$2:$B$1000"),2,FALSE)</code>
....but note that INDIRECT won't work if the source workbook is closed

excel - Why doesn't this vlookup formula using the indirect function work? - Stack Overflow

answered Mar 19 '12 at 19:39 barry houdini

<tbody style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
</tbody>