is MATCH a volatile function?

D

Dave F

I have the following formula: =IF(ISNUMBER(MATCH(B2,'1 31 07 FAS sheet
1'!$AG$2:$AG$65536,0)),"Matched",IF(ISNUMBER(MATCH(B2,'1 31 07 FAS sheet
2'!$AG$4:$AG$58232,0)),"Matched","Unmatched"))

If I manipulate other data outside the ranges in the above formula, the
whole workbook recalculates. I've never seen MATCH referred to as volatile.

Thoughts?
 
D

Dave F

This is helpful thanks. According to the link, the formula below must have
become volatile because it refers to other worksheets and I was inserting
columns.
 
J

Jim Thomlinson

In 2003 inserting columns will force a calculation (2003 was the first to
allow you to aggregate based on whether the cell was hidden or not). It will
not make the function volatile. However by inserting a row you may have
changed the dependant range which will dirty the cell with the match and
force the recalc.
 
Top