Formula Explanation

The SMALL in the above returns the first row number in which the value in 'CBU LC'!DI$1 can be found in 'S:\[JOBLIST-2005.xls]JOBLIST'!$O$6:$O$10000
The value returned by ROW(1:1) dictates k in the SMALL - so as this is copied down so k increments from 1 to 2 to 3 etc...

The value returned by SMALL is then used in conjunction with an outer INDEX to retrieve the appropriate value of interest

INDEX('S:\[JOBLIST-2005.xls]JOBLIST'!$A$1:$E$10000,SMALL(...),5)

this then returns the value from the intersect of Row n (where n is determined by the SMALL result) and Column 5 (E)

It follows that if you have say 2 matches then SMALL(range,3) will return an error given no 3rd value exists.

For this reason whomever created this double evaluated the entire formula.
First testing to see if the formula returned an error - if so this was "trapped" and a Null returned, if no error the formula is calculated again to return the valid result.

This approach is very inefficient and my hunch is given the references used (DI$1 etc) that this is being used in a matrix of some sort which is very bad news in terms of calculation performance.

Avoiding double evaluation is generally a good idea... when Arrays are involved this becomes even more of a "no-brainer".

You should always look to conduct as a few Array formulae as possible.

In this instance it would be better to store the count of matches found in another cell *
Use this value to determine as to whether or not the Array needs to be calculated in the first instance
if k > count then do not calculate Array thus removing the need for double evaluation altogether and limiting no. of Arrays being calculated.


*given file is external and closed one would need to use a SUMPRODUCT rather than a COUNTIF to calculate this, eg:

=SUMPRODUCT(--('S:\[JOBLIST - 2005.xls]JOBLIST'!$O$6:$O$1000='CBU LC'!DI$1))