Can someone finish this formula for me........

[KnAsTa]

Hi, i posted this earlier on, but my problem didnt really get clarified awefully well. I was wondering if someone would be kind enough to show me the right code for this formula. Its not working completely write. Its not syntactically wrong, no errors are occuring, except within the cells. Here is the code:

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

--OK, the first part works( i am getting values from an external spreadsheet), it returns the correct numbers (either 3 or 0)

IF(VLOOKUP(A6,week1.xls!$A$1:$D$10,4,0)>VLOOKUP(A6,week1.xls!$A$1:$D$10,3,0),3,0))

--HOWEVER, this part does not work, it returns #N/A, and i really do not know why. I have spent the last hour trying to work it out. Can somebody please help! The formula is now a mess in my head

Thanks a lot

 

[Ian Mac]

On 2002-04-12 07:35, KnAsTa wrote:
Hi, i posted this earlier on, but my problem didnt really get clarified awefully well. I was wondering if someone would be kind enough to show me the right code for this formula. Its not working completely write. Its not syntactically wrong, no errors are occuring, except within the cells. Here is the code:

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

--OK, the first part works( i am getting values from an external spreadsheet), it returns the correct numbers (either 3 or 0)

IF(VLOOKUP(A6,week1.xls!$A$1:$D$10,4,0)>VLOOKUP(A6,week1.xls!$A$1:$D$10,3,0),3,0))

--HOWEVER, this part does not work, it returns #N/A, and i really do not know why. I have spent the last hour trying to work it out. Can somebody please help! The formula is now a mess in my head

Thanks a lot

you really do seem in quite a pickle.

lets break it down a bit.

Your first IF() statement
ISNUMBER(MATCH(A6,week1.xls!$A$1:$A$10,0))

Presumbly, if the value that is in A6 is in the range $A$1:$A$10 on sheet week1.xls! you then want it to test whether:

VLOOKUP(A6,week1.xls!$A$1:$D$10,3,0)>VLOOKUP(A6,week1.xls!$A$1:$D$10,4,0) is TRUE or FALSE

NOTE: I've highlighted above (in your quote) what I assume you believe is the same as VLOOKUP(A6,week1.xls!$A$1:$D$10,3,0)>VLOOKUP(A6,week1.xls!$A$1:$D$10,4,0). These 2 are different as the first 1 test whether column 3 is greater than column 4 (in terms of the lookup values). in the second it's the other way round.

OK,

so far with:

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

you've checked whether the number exists in the first column of the lookup table and if it's true then you've checked to see if the value in col 3 is greater than 4. if it is put 3 if it isn't put 0.

What happens if the number ISN'T there in the first place? i.e. the ISNUMBER(MATCH(A6,week1.xls!$A$1:$A$10,0)) is FALSE, you don't have an argument for this.

Try using this:

=IF(NOT(ISNA(LOOKUP(A6,week1.xls!$A$1:$A$10,0))),IF(VLOOKUP(A6,week1.xls!$A$1:$D$10,3,0)>VLOOKUP(A6,week1.xls!$A$1:$D$10,4,0),3,0),"NOT THERE")

and replace the "not there" with what you need.
I could expand on this if you need it BUT I've ben typing for yonks!!

does this put you some way towards your goal.