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:
[TABLE="width: 113"]
<tbody>[TR]
[TD="width: 113"][test.xlsx]Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

and
Input2:
[TABLE="width: 94"]
<tbody>[TR]
[TD="width: 94"]!$H$10:$J$100[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: fw, width: 665"]
<tbody style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">[TR="bgcolor: transparent"]
[TD="class: vt, bgcolor: transparent"]excel - Why doesn't this vlookup formula using the indirect function work? - Stack Overflow

[/TD]
[TD="class: post-signature, bgcolor: transparent, align: right"]answered Mar 19 '12 at 19:39


barry houdini

[/TD]
[/TR]
</tbody>[/TABLE]