Nth Small in array excluding zeros

[Calgary_Neil]

I am wanting to find the column of the Nth (2nd) occurrence of "Y". The array that SMALL works on is correct, but SMALL returns 0, which is the 2nd smallest number after 0.
How do I remove the 0's or do this another way? TProd is a named range of 28 Columns, which has 2 "Y" in it (12, 19)

SMALL( (N( TProd="Y") * COLUMN( TProd)), 2)


Thankyou for reading
Neil (Excel 365)

 

[Eric W]

Try:

=AGGREGATE(15,6,COLUMN(TProd)/(TProd="y"),2)

 

[Calgary_Neil]

Thanks. Never would of got that.
Away right now, but I think you are dividing by 0 (or 1) and then eliminating the errors. Intresting

thanks

 

[Calgary_Neil]

Using your method this also works
SMALL( IFERROR( COLUMN( TProd) / ( TProd="Y"), 99), 2)

Neil

 

[Eric W]

Yep, you got the gist of it! Glad to help.