It only showed Chelsea for me.

--
HTH

Bob Phillips

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

"Michael" wrote in message
...
On Mon, 29 Jan 2007 10:50:15 -0000, "Bob Phillips"
wrote:

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,"")

Bob,
I must apologise for the formatting as it looks like a right dog's
dinner.

In D3:D40 are the home team this is duplicated to BD3:BD40. E3:E40
duplicate to BE3:BE40 Home Score. As the MIN function does not like
blanks cells I used the formula =IF(ISNUMBER(E3),E3,0).

The above formula produced
Middlesbrough
Portsmouth
Sheff Utd
West Ham
Chelsea
Arsenal
Fulham
Wigan

Chelsea is the only team that Aston Villa drawn home and away.
The others have play Aston Villa only once and drew.


The second formula:

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

Produced the following.

Portsmouth
Sheff Utd
West Ham
Chelsea
Arsenal
Wigan

However in this example is outputting the away teams that have played
as of yet.

Chelsea is the only team that Aston Villa drawn home and away.
The others have play Aston Villa only once and drew.

I hope that this makes sense.

Thanks

Michael