concatenate an "actionable" formula
 

I need to create a VLOOKUP that uses a variable for the TABLE ARRAY element
in the formula.

I can create the proper formula but it won't execute the lookup. It begins
with an = sign but it acts like it is only text. How can I take:

=concatenate("=VLOOKUP($B$11,Table" & b31 & ",3,FALSE")) and have the result
perform the lookup?

I've tried using an indirect(b31) but that isn't it either.

Help!

Thanks,
Ken

concatenate an "actionable" formula
 

Have you considered using a named range? It can be pre-defined or defined
with VBA.

"KG Old Wolf" wrote:

I need to create a VLOOKUP that uses a variable for the TABLE ARRAY element
in the formula.

I can create the proper formula but it won't execute the lookup. It begins
with an = sign but it acts like it is only text. How can I take:

=concatenate("=VLOOKUP($B$11,Table" & b31 & ",3,FALSE")) and have the result
perform the lookup?

I've tried using an indirect(b31) but that isn't it either.

Help!

Thanks,
Ken

concatenate an "actionable" formula
 

It looks like you could use something like:

Try:
=VLOOKUP($B$11,indirect("Table"&b31),3,FALSE)



KG Old Wolf wrote:

I need to create a VLOOKUP that uses a variable for the TABLE ARRAY element
in the formula.

I can create the proper formula but it won't execute the lookup. It begins
with an = sign but it acts like it is only text. How can I take:

=concatenate("=VLOOKUP($B$11,Table" & b31 & ",3,FALSE")) and have the result
perform the lookup?

I've tried using an indirect(b31) but that isn't it either.

Help!

Thanks,
Ken

--

Dave Peterson

concatenate an "actionable" formula
 

you need the IDIRECT function and you don't need concatenate


so I think your aim is to replace this

=VLOOKUP(B11, table4,3,false) 'A

with a variable for table4

so breakign it down

=VLOOKUP(B11,INDIRECT("table4"),3,false)
should be the same as 'A above and then

=VLOOKUP(B11,INDIRECT("table" & "4"),3,false)
then
=VLOOKUP(B11,INDIRECT("table" & B31),3,false)

if B31 has the value 5, then the lookup would be table5







"KG Old Wolf" wrote:

I need to create a VLOOKUP that uses a variable for the TABLE ARRAY element
in the formula.

I can create the proper formula but it won't execute the lookup. It begins
with an = sign but it acts like it is only text. How can I take:

=concatenate("=VLOOKUP($B$11,Table" & b31 & ",3,FALSE")) and have the result
perform the lookup?

I've tried using an indirect(b31) but that isn't it either.

Help!

Thanks,
Ken

concatenate an "actionable" formula
 

sorry, typo. IDIRECT should of course be INDIRECT , which is typed
correctly in the formula that I gave.

"Patrick Molloy" wrote:

you need the IDIRECT function and you don't need concatenate


so I think your aim is to replace this

=VLOOKUP(B11, table4,3,false) 'A

with a variable for table4

so breakign it down

=VLOOKUP(B11,INDIRECT("table4"),3,false)
should be the same as 'A above and then

=VLOOKUP(B11,INDIRECT("table" & "4"),3,false)
then
=VLOOKUP(B11,INDIRECT("table" & B31),3,false)

if B31 has the value 5, then the lookup would be table5







"KG Old Wolf" wrote:

I need to create a VLOOKUP that uses a variable for the TABLE ARRAY element
in the formula.

I can create the proper formula but it won't execute the lookup. It begins
with an = sign but it acts like it is only text. How can I take:

=concatenate("=VLOOKUP($B$11,Table" & b31 & ",3,FALSE")) and have the result
perform the lookup?

I've tried using an indirect(b31) but that isn't it either.

Help!

Thanks,
Ken