Help with index(small(if

[robz228]

I am trying to duplicate all rows where column u shows "december" and i am trying to use this formula to return the data in row b where column u = december:


sheet 1 a1 is just a helper cell containing the word december

[pre]

=IF(ISERROR(INDEX($B$16:$B$65000,SMALL(IF($U$16:$U$65000=Sheet1!$A$1,ROW($U$16:$U$65000)),ROW(1:1)))),"",
INDEX($B$16:$B$65000,SMALL(IF($U$16:$U$65000=Sheet1!$A$1,ROW($U$16:$U$65000)),ROW(1:1))))

[/pre]

It seems to be functioning correctly, however its only returning 0 instead of the data in the desired cells


heres what b16 has, and what it should display


Test Rob Zaenglein 0 0 0 0 0 0 0 0 0 December


So b2 has the formula, and ideally should display Rob Zaenglein, but its just displaying 0

 

[NARAYANK991]

Hi Rob ,


The problem is in this formula :


=INDEX($B$16:$B$65000,SMALL(IF($U$16:$U$65000=Sheet1!$A$1,ROW($U$16:$U$65000)),ROW(1:1)))


The smallest value , if U16 has the value december , will be 16 ; indexing $B$16:$B$65000 with the value 16 will either give the wrong value , or no value at all.


The smallest index value has to start from 1 ; so whenever you start from any row other than 1 , you need to use the following construct :


ROW($U$16:$U$65000) - MIN(ROW($U$16:$U$65000)) + 1


which in effect will start the indexing from 1.


So , your full formula will become :


=IF(ISERROR(INDEX($B$16:$B$65000,SMALL(IF($U$16:$U$65000=Sheet1!$A$1,ROW($U$16:$U$65000) - MIN(ROW($U$16:$U$65000)) + 1),ROW(1:1)))),"",

INDEX($B$16:$B$65000,SMALL(IF($U$16:$U$65000=Sheet1!$A$1,ROW($U$16:$U$65000) - MIN(ROW($U$16:$U$65000)) + 1),ROW(1:1))))


Narayan

 

[robz228]

Awesome, thank you so much.