Reply to thread

Message: [QUOTE="Victor Delta, post: 7422920"] "Ron Rosenfeld" wrote in message This formula must be **array-entered**: Sheet2!A1: =IFERROR(SMALL((Sheet1!$C$1:$C$100="HOME")*ROW(INDIRECT("1:100")), COUNTIF(Sheet1!$C$1:$C$100,"<>HOME")+ROWS($1:1)),"") Change all of the 100's to the highest row necessary. You could reference the entire row (Sheet1!$C:$C), but it will run more slowly ---------------------------------------- To **array-enter** a formula, after entering the formula into the cell or formula bar, hold down <ctrl><shift> while hitting <enter>. If you did this correctly, Excel will place braces {...} around the formula. ----------------------------- Ron Very many thanks for this. Have entered the formula as suggested by each cell shows #NAME? - Have I done something silly? V [/QUOTE]