Excel - I need the ROW formula to go to the next row, while i drag across.

[DV1987]

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

0down votefavorite ​

I have the below formula, which i would like to drag horizontally but have the ROW(1:1) which needs to change vertically e.g ROW(2:2) =IF(ISERROR(INDEX($B$1:$C$800,SMALL(IF($C$1:$C$800=S$2,ROW($C$1:$C$800)),ROW(1:1)),1)),"",INDEX($B$1:$C$800,SMALL(IF($C$1:$C$800=S$2,ROW($C$1:$C$800)),ROW(1:1)),1)) Your help is greatly appreciated.

<tbody style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;">
</tbody>

</body>

down votefavorite ​

I have the below formula, which i would like to drag horizontally but have the ROW(1:1) which needs to change vertically e.g ROW(2:2) =IF(ISERROR(INDEX($B$1:$C$800,SMALL(IF($C$1:$C$800=S$2,ROW($C$1:$C$800)),ROW(1:1)),1)),"",INDEX($B$1:$C$800,SMALL(IF($C$1:$C$800=S$2,ROW($C$1:$C$800)),ROW(1:1)),1)) Your help is greatly appreciated.

<tbody style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;">
</tbody>

 

[DV1987]

Sorry if the above is unclear. Please see below again.

I have the below formula, which i would like to drag horizontally but have the ROW(1:1) which needs to change vertically e.g ROW(2:2)
=IF(ISERROR(INDEX($B$1:$C$800,SMALL(IF($C$1:$C$800=S$2,ROW($C$1:$C$800)),ROW(1:1)),1)),"",INDEX($B$1:$C$800,SMALL(IF($C$1:$C$800=S$2,ROW($C$1:$C$800)),ROW(1:1)),1))
Your help is greatly appreciated.

 

[Aladin Akyurek]

Either...

In X1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$1:$B$800,SMALL(IF($C$1:$C$800=S$2,ROW($C$1:$C$800)-ROW($C$1)+1),ROWS($X$1:X1))),"")

Or...

=IFERROR(INDEX($B$1:$B$800,SMALL(IF($C$1:$C$800=S$2,ROW($C$1:$C$800)-ROW($C$1)+1),ROWS($1:1))),"")

P.S. The site from where you probably copied the formula is totally wrong in propogating this._{^{<strike>
</strike>}}