# INDEX returning wrong values

1. ## INDEX returning wrong values

I have a spreadsheet with two sheets. On the first sheet, there is a list of policies and the date they are up for review. Using conditional formatting, I have managed to highlight the policies that are due.
On the second sheet, I am trying to list all of those policies that are due. Below is my formula:

=IFERROR(INDEX(Policies!A\$3:A\$130, SMALL(IF(Policies!D\$1=Policies!E\$3:E\$130, ROW(Policies!A\$3:A\$130)-ROW(Policies!\$A\$4)+1), ROW(1:1))),"")

This formula works to a degree. The first policy it retrieves is correct, but every policy after that is off by one row. I put \$ before every reference to the cells on the Policies sheet because when I dragged the formula down it incremented all by one which made it invalid. I suspect my problem is something to do with the ROW part of the formula, but I've been trying for days and haven't managed to figure it out. Is anyone able to point me in the right direction?

2. ## Re: INDEX returning wrong values

Try:

=IFERROR(INDEX(Policies!A\$3:A\$130, SMALL(IF(Policies!D\$1=Policies!E\$3:E\$130, ROW(Policies!A\$3:A\$130)-ROW(Policies!\$A\$3)+1), ROWS(\$B\$1:\$B1))),"")

again, array-entered.

3. ## Re: INDEX returning wrong values

I guess error came from this:
ROW(Policies!A\$3:A\$130)-ROW(Policies!\$A\$4)+1

Change \$A\$4 to \$A\$3 then it should work.

4. ## Re: INDEX returning wrong values

Originally Posted by rorya
Try:

=IFERROR(INDEX(Policies!A\$3:A\$130, SMALL(IF(Policies!D\$1=Policies!E\$3:E\$130, ROW(Policies!A\$3:A\$130)-ROW(Policies!\$A\$3)+1), ROWS(\$B\$1:\$B1))),"")

again, array-entered.
Perfect, thank you!