SMALL(IF(IFERROR(SEARCH($G$2,$A$1:$A$20)>0,FALSE),ROW($A$1:$A$20)),ROW()-2)

[ronmaltase]

Hello,
I'm using one of Chandoo's great formulas, like this:
{=SMALL(IF(IFERROR(SEARCH($G$2,$A$1:$A$20)>0,FALSE),ROW($A$1:$A$20)),ROW()-2)}

to return the row number from an array.

Rather than have the results in a vertical column, i'd like to drag a formula across a horizontal row.

I was trying something like this:
{=SMALL(IF(IFERROR(SEARCH($B$2,$C$2:$C$562)>0,FALSE),ROW($C$2:$C$562)),COLUMN()-2)}

But it’s not working…

Any ideas?
-Thanks, Ron M.

 

[Luke M]

This part:
COLUMN()-2

is where I would suspect. Is your formula start in column C? To be safe, I'd change formula to this:
{=SMALL(IF(IFERROR(SEARCH($B$2,$C$2:$C$562)>0,FALSE),ROW($C$2:$C$562)),COLUMNS($A$1:A$1))}

Still will increment a count, but now it's not dependent on where formula is placed.