Modification of a IF ISNUMBER SEARCH formula

**zigojacko** · 04-15-2013, 09:59 AM

I'm currently using the following formula in Excel...

=IF(ISNUMBER(SEARCH("16 x 16",Sheet1!K2)),"Square", "Not Square")

I want to be able to copy this down thousands of rows but this results in 1 being added to K2 above each row whereas I need it to stay the same for an additional 10 rows before increasing to K3, and then a further 10 rows before increasing to K4 (etc).

Thanks in advance.

**daddylonglegs** · 04-15-2013, 10:14 AM

Try using an INDEX function so that you only increment every 10 rows, e.g. if first formula is in F2 (change red part of formula as required) use this version in that cell and copied down

=IF(ISNUMBER(SEARCH("16 x 16",INDEX(Sheet1!K$2:K$1000,INT((ROWS(F$2:F2)-1)/10)+1))),"Square", "Not Square")