First of all, while your formula does not hit any Excel limitation I know of

(such as 7 levels of embedded conditions), vlookup and hlookup are fairly

intensive formulas. Do you have this in only one cell, or is this in every

cell in that column (e.g. the 4500 rows)? If you have this formula in 4500

cells, then I'm not surprised that this would take a long time to calculate.

The extra instances of Excel may be Excel opening the source named range

workbooks, although I can't be sure. That will add some overhead, probably

not that much but it may be worth testing. Pull a copy of each of those

source worksheets into your main workbook, adjust your named ranges to point

within that same workbook, and see if that is any faster.

Is your main workbook and named range workbooks all local, or are you

accessing over a network?

Another thing (at least with Excel2007, I don't know about 2003) is that

clicking in the workbook (changing the focus, or selecting another cell)

seems to reset the calculations, and you have to wait all over again (you may

already be aware, I'm just writing down anything I can think of)

Overall though, I think the basic issue is the number of vlookups and

hlookups you are doing (2 per calculation/per cell). You could always try a

combination of MATCH and INDEX to replace the V and H-Lookups, but I don't

know if that would be any faster...

Are your source ranges sorted? I would suspect that having the source ranges

sorted in ascending order may speed up the lookups, even if you are using

FALSE to ensure an exact match (I'm making some assumptions about what search

algorithm is used by Excel)

My last suggestion is to consider a VBA (programming) solution; depending on

your data format, there may be a way to speed up the processing- and because

the VBA would only run when prompted, you would not have to worry about Excel

locking up to recalculate everything when you won't want it to (if you change

a cell value that doesn't affect your lookups, or if you want to enter a

bunch of values and not have Excel recalculate in between each entry)

Without knowing your data set I won't make assumptions; I notice that your

HLookup does not include the final optional parameter for exact match.

HTH,

Keith

Rolf said:

Excel 2003/SP2, running under Windows XP takes forever to calculate a

formula when it is copied to a new cell. My computer is a P4/1.3Gig with 768MB

memory.

The problem occurred within the last day or so, and I can not figure out a

possible cause. Rebooting and running the error correction program for the

office installation had no effect. When pasting, the CPU usage jumps to 100%,

the task manager shows several instances of Excel as running. Have I

exceeded the programs capability with this Lookup formula?

The formula:

[=IF(J3763="","",

IF(J3763<$A$1,VLOOKUP(A3763,Data1,HLOOKUP(J3763,Data1,2),FALSE),

IF(J3763<$A$2,VLOOKUP(A3763,Data2,HLOOKUP(J3763,Data2,2),FALSE),

IF(J3763<$A$3,VLOOKUP(A3763,Data3,HLOOKUP(J3763,Data3,2),FALSE),

VLOOKUP(A3763,Data4,HLOOKUP(J3763,Data4,2))))))]

is in a worksheet with data in cells A1 to K4500, extracts the price for the

stock symbol in col A for the date entered in column J.

Column J does not contain any dates, except when manually entered.

Data1, 2, 3 and 4 are defined names for cell blocks in four worksheets of an

external workbook.

Cells A1 to A4 contain reference dates for the last record day contained in

the

referenced worksheet.

The workbook has 5 worksheets,

Worksheet A has 4 columns of formulas copied into 4500 rows.

Worksheet B has 2 columns of formulas copied into 600 rows

Worksheet C has 2 columns of formulas copied into 40 rows

The other worksheets contain only data.

Any help will be greatly appreciated

Rolf

PS. Posted originally under General Questions