Excel hangs crashes when pasting formulas

[Lutash]

I have an opened workbook in Excel 07 with my formulas C thru Z. When I copy
and try to paste to a new workbook, it hangs or crashes and the memory usage
goes to over 1g. If I wait thru the hang, then it hangs when I try to insert
or delete any rows. Went to older machine and Excel 03 today to try and
everything worked just fine.

Event viewer gives following error
ID: 1, Application Name: Microsoft Office Excel, Application Version:
12.0.6524.5003, Microsoft Office Version: 12.0.6425.1000. This session lasted
4959 seconds with 900 seconds of active time. This session ended with a
crash.

One of the shorter formulas that i am using
=LOOKUP($U2,{1.1,1.3,1.35,1.56,2,2.15,2.23,2.3,2.35,2.4,2.55,2.58,2.6,2.64,3,3.15,3.2,3.35,3.5,3.55,3.6,3.95,4.1,4.15,4.25,4.35,4.92,5.5,6,6.05,6.2,6.3,12,12.75,13.5,51},{35,18,34,32,1,2,15,3,4,30,16,31,23,25,19,10,24,5,6,12,29,11,17,20,27,7,26,28,8,9,14,22,33,21,37,13})

Can someone please help ?

 

[Peter T]

That shorter formula works fine for me in both 2003 & 2007. How long are
your longer formulas, limit is something under 1024. But why not store the
two arrays in cells and refer to them there.

Regards,
Peter T

 

[Lutash]

Its much larger, but under 1024.
I'm not sure I know how to store and refer.
Could you advise ?

 

[Peter T]

I just did this with your formula -

Copy the first array (everything between the first set of curly brackets) to
A3, and the second array to A4
Select A3:A4
Data, Text to Columns, Delimited, use the comma separator, OK
Your values should extend across to A3:AJ4
Select A3:AJ4 and Copy
Select A6
PasteSpecial Transpose

you should now have values in two columns from row 6 to 41
Select A1:A41
In the name box left of input bar, name it (say) "LUvalues"
and similarly name B6:B41 "LUvectors"

in A1 put 1.35
in some other cell
=LOOKUP(A1,LUvalues,LUvectors)
should return 34

Regards,
Peter T