Excel 2007 : Looking for a non volatile replacement for OFFSET

Hi

I have a large spreadsheet but one that runs very efficiently. That was until I introduced a column of formulas that include the OFFSET function. Now the spreadsheet need over half a minute to recalculate itself every time i change a single value. I know it is the OFFSET function that is causing this as it is volatile. Does anybody have a replacement formula for the one below that achieves the same outcome without using OFFSET?

=OFFSET($WP$5,MATCH(E5&F5&G5,INDEX($WL$5:$WL$1000&$WM$5:$WM$1000&$WN$5:WN$1000,0),0)-1,0,1,1)

Any help would be greatly appreciated.

Doug

Without seeing the formula in context it's hard to be precise, perhaps the following might work ?

=LOOKUP(2,1/((WL5:WL1000=E5)*(WM5:WM1000=F5)*(WN5:WN1000=G5)),WP5:WP1000)

the above assumes that the combination of E & F & G occurs only once ... if you have multiple records the above returns the value from WP associated with the last instance of the combination.

I would like to add that you would be best served adding a concatenation of E/F&G and WL/WM & WN in 2 columns and reverting to a traditional INDEX/MATCH as this would most likely prove more efficient still.

Hi Donkey

You were right, if you had seen the context you would have been set. By adding a few $ symbols into your formula I was able to get it to do exactly what I wanted.

=LOOKUP(2,1/(($WL$5:$WL$2000=E5)*($WM$5:$WM$2000=F5)*($WN$5:$WN$2000=G5)),$WP$5:$WP$2000


The spreadsheet still has a 1 to 2 second delay but a little bit of tidying up will sort that. Thank you very much for your help, I can stop tearing my hair out now :-)

Doug