"Ron Rosenfeld" wrote in message
...

On Wed, 9 Oct 2013 09:14:29 +0100, "Victor Delta" wrote:



Very many thanks for this. Have entered the formula as suggested by each
cell shows #NAME? - Have I done something silly?

V

Possibly you have a typo, or you are using a version of Excel prior to 2007
that does not support IFERROR.

If that is the case, the following **array-entered** formula should work, as
mentioned by joeu

=IF(ISERROR(SMALL((Sheet1!$C$1:$C$100="HOME")*ROW( INDIRECT("1:100")),
COUNTIF(Sheet1!$C$1:$C$100,"<HOME")+ROWS($1:1))), "",
SMALL((Sheet1!$C$1:$C$100="HOME")*ROW(INDIRECT("1: 100")),
COUNTIF(Sheet1!$C$1:$C$100,"<HOME")+ROWS($1:1)))

If that is the problem, then a variant of Hans formula should also work:

=IF(ISERROR(SMALL(IF(Sheet1!C$1:C$100="HOME",ROW($ 1:$100)),ROW(A1))),
"",SMALL(IF(Sheet1!C$1:C$100="HOME",ROW($1:$100)), ROW(A1)))

------------------------------

Ron

Many thanks for that.

V