Solved: Index Function with Multiple "Ifs" won't Return More than First Result

Hi,

I am attempting to get an INDEX function with multiple "IF" statements to return more than one result. I've scanned through previous posts and found some help wtih building the multiple IF statements. That portion of the below formula works. However, I can only get the INDEX formula to return the first, single result.

Formula:
=IFERROR(INDEX(Func_Area,SMALL(IF(Const_Start<=DATE(YEAR($B$2),MONTH($B$2),DAY($B$2)), IF(Cost_End>=DATE(YEAR($C$2),MONTH($C$2),DAY($C$2)),ROW(Func_Area)-MIN(ROW(Func_Area))+1,"")),ROWS($A$4:A4)))," ")

Func_Area,Const_Start,Cost_End are all Named Ranges on Sheet "Proposed". My intention is to return a list of projects on Sheet Test1 for all projects starting construction after a certain date and completing construction before a certain date. I suspect something in my ROWS fuction or ROW-MIN+1 is wrong.

This formula returns the first result in the first row, but every subsequent row is blank...see attached spreadsheet.

What am I missing?

Given your stated aim you should be using Data Filtering functionality rather than using specific functions like INDEX.

Try Data Advanced Filter and specify an output range and a criteria range that uses your two dates.

I think you need to reverse the > and < signs if you want projects that start on or after 1/1/2014 and end on or before 31/12/2014. Try this formula in A4

=IFERROR(INDEX(Func_Area,SMALL(IF(Const_Start>=$B$2, IF(Cost_End<=$C$2,ROW(Func_Area)-MIN(ROW(Func_Area))+1)),ROWS(A$4:A4))),"")

confirm with CTRL+SHIFT+ENTER and copy down

......then copy across and change the INDEX range for each column