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


"Derek Y via OfficeKB.com" wrote:

and if this helps at all

J_Z_1 refers to cell W1 on sheet "J-Z"

CODECOL refers to cell A6 on sheet "Single Data"

Final_all refers to 1:65536 on the sheet "Final Merge"

Final_1 refers to 1:1 on sheet "final Merge"





Derek Y wrote:
Hello,

I have this formula and it's very long. Below is a copy of it

=IF(J_Z_1=CODECOL,INDEX(FINAL_ALL,MATCH(A2,'Fin al Merge Fields'!A:A,0),MATCH
("Code",FINAL_1,0)),"")

The only part of that whole entire formula that changes as I drag it down is
where it says A2. That should change to A3, A4, and so on.

I have to drag this formula down 50,000 rows in 2 worksheets. Is there
anyway I can possibly condense it by like just putting it in one cell and
telling all the other cells to get it from it except to update the A2,A3 part?

Your help is greatly appreciated.

Derek

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