Bruce, That was a huge help.

You were right in that I am using Final merge fields interchangably. The
seperate sheet is actually 'Final Merge Fields'.

I got those definitions to work just fine and it made it a bit shorter, but
your final simplification where you are using VLookup instead, I could not
get to work. All I did was this so far:

=MATCH("Code",FINAL_1,0) defined as ColNum
='CITY (A-I)'!$V$1='SINGLE DATA'!$A$6 defined as Valid

replaced those two area's of my original formula with those words.

When I make your final replacement it wont even accept it as a formula. I
guess I dont know what
FinalMergeFields is supposed to be referring to in your formula or how to
define that.

Thanks SO much for your help.

Derek



bpeltzer wrote:
It looks like you could first define two cells that have the formulas for the
IF condition and the second match.
Ex: =(J_Z_1 = CODECOL). Define that cell reference as Valid.
And =match("Code",FINAL_1,0). Define that cell as ColNum.
Then your function code be modestly simplified as
=if(Valid,index(final_all,match(...),ColNum),"" ).
I *think* that you're using FinalMergeFields interchangably with FinalMerge,
in which case this simplies a bit further:
=if(Valid,vlookup(a2,FinalMergeFields,ColNum,0)," ").
HTH. --Bruce

and if this helps at all

[quoted text clipped - 23 lines]

Derek

--
Message posted via http://www.officekb.com