Using AND to check 2 conditions...

Hello all-
I'm trying to work an AND statement in to a formula so that if 2 conditions are met (fuel and type) then the date of that record will be displayed along with the other matched dates in ascending order on a report tab. This statement works for a single condition,

=INDEX(data!$A$1:$A$100,SMALL(IF(data!$B$1:$B$100=$A$3,ROW(data!$A$1:$A$100)),ROWS($B$2:B3)))

and this is the statement that I've been trying to get to work for two conditions,

=INDEX(data!$A$1:$A$100,SMALL(IF(AND(data!$C$1:$C$100=$B$3,data!$B$1:$B$100=$A$3),ROW(data!$A$1:$A$100)),ROWS($B$2:B3)))


Where am I dropping the ball here? I've also attached a workbook with this example in it.

Thanks in advance for wisdom and insight shared.




<data tab>

date | type | fuel
6/21/2011 | Blue Volvo | diesel
6/21/2011 | Red Citroen | electric
6/23/2011 | White Landcruiser | gasoline
6/23/2011 | Green Ford | diesel
6/23/2011 | Silver Toyota | electric
6/23/2011 | White Landcruiser | diesel
6/28/2011 | Blue Volvo | corn oil
6/28/2011 | Red Citroen | electric
6/28/2011 | Yellow Mazda | gasoline
6/28/2011 | Green Ford | diesel
7/2/2011 | White Landcruiser | electric
7/2/2011 | Silver Toyota | electric
7/2/2011 | Green Ford | diesel
7/2/2011 | Yellow Mazda | corn oil
7/3/2011 | White Landcruiser | diesel
7/3/2011 | Blue Volvo | gasoline
7/12/2011 | White Landcruiser | electric

<report tab>

White Landcruiser | electric
7/2/2011
7/12/2011

White Landcruiser | diesel
6/23/2011
7/3/2011

Red Citroen | electric
6/21/2011
6/28/2011

Got it-

The below worked great to parse out the dates
={INDEX(data!$A$1:$A$100,SMALL(IF((data!$E$1:$E$100=$B$2)*(data!$C$1:$C$100=$A$2),ROW(data!$A$1:$A$100)),ROWS($B$1:B1)))}

I'm working on copying the count value for each specific incidence of date-type-fuel.

Here is what I have so far working with nested IF statements:
=INDEX(data!$A$1:$A$100,IF(data!$E$1:$E$100=$B$2,IF(data!$C$1:$C$100=$A$2,IF(data!$A$1:$A$100=$C2),ROW(data!$D$1:$D$100)))),ROWS($B$1:B1))))
This would go in D2 on the report tab.

Help. File uploaded.
Thanks in advance.


<data tab>

date | type | fuel | count
6/21/2011 | Blue Volvo | diesel | 13
6/21/2011 | Red Citroen | electric | 12
6/23/2011 | White Landcruiser | gasoline | 11
6/23/2011 | Green Ford | diesel | 12
6/23/2011 | Silver Toyota | electric | 15
6/23/2011 | White Landcruiser | diesel | 12
6/28/2011 | Blue Volvo | corn oil | 17
6/28/2011 | Red Citroen | electric | 19
6/28/2011 | Yellow Mazda | gasoline | 12
6/28/2011 | Green Ford | diesel | 10
7/2/2011 | White Landcruiser | electric | 11
7/2/2011 | Silver Toyota | electric | 12
7/2/2011 | Green Ford | diesel | 15
7/2/2011 | Yellow Mazda | corn oil | 12
7/3/2011 | White Landcruiser | diesel | 23
7/3/2011 | Blue Volvo | gasoline | 12
7/12/2011 | White Landcruiser | electric | 13

<report tab>
type | fuel | date | count
White Landcruiser | electric | 7/2/2011 | 11
| | 7/12/2011 | 13

White Landcruiser | diesel | 6/23/2011 | 12
| | 7/3/2011 | 23

Red Citroen | electric | 6/21/2011 | 12
| | 6/28/2011 | 19

This,

=INDEX(data!$F$1:$F$100,SMALL(IF((data!$E$1:$E$100=$B$2)*(data!$C$1:$C$100=$A$2)*(data!$A$1:$A$100=$C2),ROW(data!$F$1:$F$100)),ROWS($B$1:B1)))

returns only the correct first match for all three conditionals and "#NUM!" thereafter.

Got it.

{=INDEX(data!$F$1:$F$100,SMALL(IF((data!$E$1:$E$100=$B$2)*(data!$C$1:$C$100=$A$2)*(data!$A$1:$A$100=$C2),ROW(data!$F$1:$F$100)),ROWS($B1:B1)))}

Is there a more concise solution?