VLOOKUP statement too long

[Guest]

I have the following statement, as it stands it works fine, but I would like
to expand on it and have it lookup three more cells. The problem is that
statement is then too long.
I was wondering if anyone had any suggestions on how to make the statement
shorter and still do what I need it to, or another way of accomplishing this.

=IF(ISNA(VLOOKUP(' Form 2'!R31,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form
2'!R31,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that
" & CHAR(10) & CHAR(10) &("2. ") &(' Form 2'!AF4&" "&' Form
2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) &
IF(ISNA(VLOOKUP(' Form 2'!R32,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form
2'!R32,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that
" & CHAR(10) & CHAR(10) &("3. ") &(' Form2 '!AF4&" "&' Form
2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) &
IF(ISNA(VLOOKUP(' Form 2'!R33,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form
2'!R33,'Employee List'!F1:G132,2))

I would like to add:

& CHAR(10) & CHAR(10) & "and further that" & CHAR(10) & CHAR(10) &("4.
") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4)
& CHAR(10) & CHAR(10) &
IF(ISNA(VLOOKUP(' Form 2'!R34,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form
2'!R34,'Employee List'!F1:G132,2))
& CHAR(10) & CHAR(10) & "and further that" & CHAR(10) & CHAR(10) &("5.
") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4)
& CHAR(10) & CHAR(10) &
IF(ISNA(VLOOKUP(' Form 2'!R35,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form
2'!R35,'Employee List'!F1:G132,2))
& CHAR(10) & CHAR(10) & "and further that" & CHAR(10) & CHAR(10) &("6.
") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4)
& CHAR(10) & CHAR(10) &
IF(ISNA(VLOOKUP(' Form 2'!R36,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form
2'!R36,'Employee List'!F1:G132,2))


Thanks in advance

 

[Tom Ogilvy]

put parts of your formula on a hidden sheet in mulitple cells. Then use the
results to build your final output.

or, use defined names to produced major parts of your formula and combine
the results of the defined names (insert=>Name=>define).

 

[Guest]

Tim,
Can you describe what you are attempt to do - it appears to be
constructing a "message" - so we can see if there is better solution. One
problem with complex formula is that in the (not too distant) future it will
be difficult to recall/determine what you are trying to do.

What are the LOOKUP fields/Values?

 

[Guest]

Thanks Tom
I split the formula into two cells and then used a third cell to build it. I
then use data validation to call the third cell and paste the results where I
need it. Works the way I need it to.