I assume that AE2 holds the team being checked, say Aston Villa, but what is
in D2:D40?

Maybe try this alternative

=IF(AND($BD3=$AE$2,$BE3=$BG3,MIN(IF(($BD$3:$BD$40= BF3)*($BF$3:$BF$40=$AE$2)*
($BE$3:$BE$40=$BG$3:$BG$40),1))),$BF3,"")

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Michael" wrote in message
...
I have problem that is driving me nuts, I confess I do not have the
skills to solve. I want to find the how many teams Aston Villa has
done a double draw over the season.


=IF(AND($BD3=$AE$2,$BE3=$BG3,MIN(IF(($BD$3:$BD$40= BF3)*($BF$3:$BF$40=$D3)*($
BE$3:$BE$40=$BG$3:$BG$40)*((BP3="d")*(BQ3="d")),1) )),$BF3,"")
With CRTL+ALT+ENTER.

I get the correct answers in this case Chelsea. Then I get Fulani and
Middlebrow in has only played Aston Villa once.


In row 2 column BD:BS are the header columns
The data is in BD3:BD40
HS = Home Score, AS = Away Score
Hr =Home Result (I.e W, D, or L) Column BP
AR =Away Result (I.e wd, d, or dl) Column BQ

The format has shifted. the data is the same with until the solid line
below as the games have not been played as yet.

BD BE BF BG Team HS Team AS P W
D L pp wd d dl Hr AR Hg Aged
Arsenal 1 Aston Villa 1 1 0 1 0 1
0 1 0 D d 0 0
Aston Villa 2 Reading 1 1 1 0 0 1
0 0 1 W l 1 -1
Aston Villa 2 Newcastle 0 1 1 0 0
1 0 0 1 W l 2 -2
West Ham 1 Aston Villa 1 1 0 1 0
1 0 1 0 D d 0 0
Watford 0 Aston Villa 0 1 0 1 0 1
0 1 0 D d 0 0
Aston Villa 2 Charlton 0 1 1 0 0
1 0 0 1 W l 2 -2
Chelsea 1 Aston Villa 1 1 0 1 0 1
0 1 0 D d 0 0
Aston Villa 1 Tottenham 1 1 0 1 0
1 0 1 0 D d 0 0
Aston Villa 1 Fulham 1 1 0 1 0 1
0 1 0 D d 0 0
Liverpool 3 Aston Villa 1 1 1 0 0
1 0 0 1 W l 2 -2
Aston Villa 2 Blackburn 0 1 1 0 0
1 0 0 1 W l 2 -2
Everton 0 Aston Villa 1 1 0 0 1 1
1 0 0 L w -1 1
Wigan 0 Aston Villa 0 1 0 1 0 1
0 1 0 D d 0 0
Aston Villa 1 Middlesbrough 1 1 0 1 0
1 0 1 0 D d 0 0
Aston Villa 1 Man City 3 1 0 0 1
1 1 0 0 L w -2 2
Portsmouth 2 Aston Villa 2 1 0 1 0
1 0 1 0 D d 0 0
Sheff Utd 2 Aston Villa 2 1 0 1 0
1 0 1 0 D d 0 0
Aston Villa 0 Bolton 1 1 0 0 1 1
1 0 0 L w -1 1
Aston Villa 0 Man Utd 3 1 0 0 1 1
1 0 0 L w -3 3
Tottenham 2 Aston Villa 1 1 1 0 0
1 0 0 1 W l 1 -1
Charlton 2 Aston Villa 1 1 1 0 0
1 0 0 1 W l 1 -1
Aston Villa 0 Chelsea 0 1 0 1 0 1
0 1 0 D d 0 0
Man Utd 3 Aston Villa 1 1 1 0 0 1
0 0 1 W l 2 -2
Aston Villa 2 Watford 0 1 1 0 0 1
0 0 1 W l 2 -2
--------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------
Newcastle 0 Aston Villa 0 0 0 0 0
0 0
Aston Villa 0 West Ham 0 0 0 0 0
0 0
Reading 0 Aston Villa 0 0 0 0 0
0 0
Aston Villa 0 Arsenal 0 0 0 0 0
0 0
Fulham 0 Aston Villa 0 0 0 0 0
0 0
Aston Villa 0 Liverpool 0 0 0 0 0
0 0
Aston Villa 0 Everton 0 0 0 0 0
0 0
Blackburn 0 Aston Villa 0 0 0 0 0
0 0
Aston Villa 0 Wigan 0 0 0 0 0
0 0
Middlesbrough 0 Aston Villa 0 0 0 0 0
0 0
Aston Villa 0 Portsmouth 0 0 0 0 0
0 0
Man City 0 Aston Villa 0 0 0 0 0
0 0
Aston Villa 0 Sheff Utd 0 0 0 0 0
0 0
Bolton 0 Aston Villa 0 0 0 0 0
0 0




Thanks in advance