I see that you've already tried, and found that your attempt in the main
sheet simply returned a text representation of a formula without evaluating
it. That's the way & works pretty much.
If you take what ker_01 offered and carry it out some, you'll find that you
can use INDIRECT to provide the 'address' part of a formula.
And aren't you making this hard on yourself? What down in lookup table,
you simply had this in B2: =C1 & D1
which would be the concatenation of C1 and D1, then
A B
Dog =VLookup(A1,lookup_table,2,False)
would return what you want.
But to carry this a little further. Lets say you have a lookup table like
this:
A E1:E6
B E1:E10
C F1:F5
D G1:G6
somewhere else you could have a setup like this:
A B
1 C =SUM(INDIRECT(VLOOKUP(A1,lookup_table,2,FALSE)))
that becomes the same as =SUM(F1:F5)
perhaps that helps?
Or back to your original issue, if you had a 3 column lookup table like this
A B C
Dog D1 E1
Log F1 G1
Bog H1 I1
You could contatenate via indirect like this:
= Indirect(VLookup(A1,lookup_table,2,False)) &
Indirect(VLookup(A1,lookup_table,3,False))
Where you only have a single cell to truly concatenate, just make the column
C entry in the lookup table point to an empty cell.
"patbarb" wrote:
Is there a way to store formulas as text in a Vlookup table and then retrieve and activate them? For example, below I do a lookup on "Dog" (A1), return the text C1&D1 from the table and slap an equal sign onto the front of it. This does not magically activate the text into a formula, unfortunately.
main spreadsheet
A B
Dog ="="&Vlookup(A1,lookup_table,2)
lookup_table
A B
Dog C1&D1
Log D1
Bog Q1
The result of this Vlookup is the text value =C1&D1, rather than the actual concatenated values of cells C1 and D1.
Thanks!
patrick
---
frmsrcurl: http://msgroups.net/microsoft.public...heet.functions
.