On 2002-04-15 01:10, KnAsTa wrote:

Ok, i'm still stuck, i have gained some advances from all your help, thanx everyone, but i think i'm going to have to explain the situation better.

I have a main spreadsheet with 19 Soccer teams listed from A5 to A24. I am typing in a formula into cell M5 which is "Away wins".

This formula takes data from an external spreadsheet called week1.xls. And it contains 4 columns and 10 rows. From A1 to B10 holds all the matches (ie all the teams paired up)

ex:

Everton Leeds 3 4

Chelsea West Ham 0 5

Southhampton Middlesbrough 2 5

Aston Villa Leicester 0 4

Liverpool Bolton 6 0

Fulham Newcastle 2 2

Sunderland Tottenham 4 3

Arsenal Blackburn 0 5

Charlton Man Utd 6 1

Derby Ipswich 3 2

The formula i am using is "supposed" to check if A5(from the main spreadsheet - which is a soccer teams name ie Arsenal) is in the external spreadsheet in $B$1:$B$10 and returns true or false if it is or isnt. If it returns true it will then calculate if they won. Ie if column 3 < column 4 (goals scored by team in column A < goals scored by team in column B)

here is my formula:

=IF(ISNUMBER(MATCH(A5,week1.xls!$B$1:$B$10,0)),IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)

I have week1.xls open aswell so it does not need a hard coded address.

I have also written another formula very similar to this which checks if the team in column A in external spreadsheet wins. And this formula works

Working formula:

=IF(ISNUMBER(MATCH(A5,week1.xls!$A$1:$A$10,0)),IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)>VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,0),"")

So the formula that does not work has only 1 thing changed in it. It changes from $A$1:$A$10 to $B$1:$B$10 . I CANNOT SEE HOW THIS DOES NOT WORK!

The non working formula returns correctly for the false clause, but returns #N/A for the true clause.

If anyone can be bothered reading all this, i am very greatful, thanx.

## Like this thread? Share it with others