Let's assume the range is A5:A8.

A5 = A
A6 = B
A7 = X
A8 = C

=SMALL(IF(A5:A8="x",ROW(A5:A8)-MIN(ROW(A5:A8))+1),ROWS(A$1:A1))

Result = 3

=SMALL(IF(A5:A8="x",ROW(A5:A8)),ROWS(A$1:A1))-MIN(ROW(A5:A8))+1

Result = 3

Those do the exact same thing, just differently.

In the first example you're calculating an *array* of offsets to match the
positions of the indexed range. In the second example you're calculating a
single offset from the result of the SMALL function.

Sample file:

xSample.xls 21kb

http://cjoint.com/?fiiwCVMXaC


--
Biff
Microsoft Excel MVP


"Domenic" wrote in message
...
In article ,
"T. Valko" wrote:

These types of formulas are already calculation intensive but you can
make
it slightly more efficient by putting the INDEX offset outside of the
SMALL
function:

SMALL(...,COLUMNS($C1:C1))-ROW(Sheet2!$A$1)+1

This way you're only calculationg a single offset instead of an array of
offsets.

--
Biff
Microsoft Excel MVP



Unless I misunderstood, I don't think it will return the desired
results...

--
Domenic
http://www.xl-central.com