Replce your formula as

=INDIRECT(VLOOKUP(Analysis!E11,split,2,FALSE)&AC6)
However note that this will fail if your vlookup fails.
HTH,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think a post is useful, please rate it as an ANSWER, it will help
others!!


"Erik" wrote:

I'm trying to simplify a series of "If" formula's buy using a lookup table
and the "&" function to provide the cell reference I'm looking for. The
formula (below) works correctly but I don't know how to make Excel use the
formula result to give me the data in the cell it's referencing, as opposed
to the formula result.

Here's the formula:
=VLOOKUP(Analysis!E11,split,2,FALSE)&AC6
which is doing the following:
VLookup:
Analysis!E11 has a choice of 4 input variables: 0,1,2,3
Split (name of the lookup table, has letter combinations like AB, AC, AD
which refer to the columns of data in another that that I want to reference)
2=the column of choice from the lookup table
False - default entry
&AC6 = a reference to the line number I want to concatinate (spelling?)
with. In this case, AC6=6
So my formula result is "V6".

Based on that formula result, I want another cell to return the value found
in cell "V6" but I can only get other cells to say "V6".

Please help.
Thanks