multiple results, multiple criteria - is there another way apart from arrays?

[s_twigge]

Hi out there!

I would like to provide a search function, based on two values.

If it was a sum, I would be fine, as I would use sumproduct, however I am trying to return multiple results which are not sums.

I have hopefully attached a sheet for you to see

Any help would be much appreciated!
Si

[samba_ravi]

show you expected results in your sample file

[Glenn Kennedy]

What excel version d you use? Your profile shows 2003, but the attachment is an xlsx (2007 and later)?

[Glenn Kennedy]

One way:

=IFERROR(INDEX(Data!C:C,SMALL(INDEX(((date<$B$3)+(date>$B$6))*10^10+ROW(date),0),ROWS(D$4:D4))),"")

NOTE: if needed on Excel 2003 and before, the error trap will need to be modified. Please amend your profile!!!

[Special-K]

These are array formulas, only way I know of, unless you go VBA route

in Summary!D4
=IFERROR(INDEX(Data!$C$5:$G$14,SMALL(IF((Data!$C$5:$C$14>=$B$3)*(Data!$C$5:$C$14<=$B$6),ROW($A$5:$A$14)),ROW(A1))-(ROW(A$5)-1),COLUMN()-3),"Not present")
Array formula, use Ctrl-Shift-Enter
and copy down the column

Copy formula into the other grid
Adjust borders
Adjust format of column H to show numbers not dates