Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

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

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    "Have a good time......all the time"
    Ian Mac

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •