"joeu2004" wrote in message ...

"Victor Delta" wrote:
"Ron Rosenfeld" wrote:
This formula must be **array-entered**: Sheet2!A1:
=IFERROR(SMALL((Sheet1!$C$1:$C$100="HOME")*ROW(IND IRECT("1:100")),
COUNTIF(Sheet1!$C$1:$C$100,"<HOME")+ROWS($1:1))," ")
[....]
cell shows #NAME? - Have I done something silly?

Are you using Excel 2003 or earlier?

IFERROR is for Excel 2007 and later.

One alternative is to use ISERROR. Not pretty! The form would be:

=IF(ISERROR(SMALL(...)),"",SMALL(...))

Note that the SMALL expression would be calculated twice if there is no
error.

I have not reviewed your needs to see if there is a better alternative.

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

Very many thanks for that. Yes, you were absolutely right, I use Excel 2003
but had forgotten to mention it - apologies.

I used your iserror version of Ron's original formula and it works just
fine.

Thanks both again,

V