VLOOKUP(A6,week1.xls!\\\$A\\\ Why doesnt this formula work!!!????
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Why doesnt this formula work!!!????

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

    Default

     
    =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
    New Member
    Join Date
    Feb 2002
    Location
    Belgium
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello,

    Your second formula doesn't work because you did not assign a Value_if_true not a Value_if_false. You just typed the condition.
    I mean, you did sth like this:
    If x>y then.... you did not write anything.
    a if funtion looks like this:
    if(condition,valueIfTRue,ValueIfFalse). You only wrote if(condition).
    Bye,
    Pierre

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

    Default

    ok, i can understand that, but how do i write the code in for it. I don't get where it goes or what i should write in. Sorry :S.

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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),"Something here if the first part is false")

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

    Default

    ok, but wasnt my second part, the If statement, the False return? Can you use an If statement in the false return?

  6. #6
    New Member
    Join Date
    Feb 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Like this?

    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),if(1=1,"Yes","No"))


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

    Default

    Isnt that what i've already done in the first place?

  8. #8
    New Member
    Join Date
    Feb 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If I need to make a complicated set of nested if's. I find it easier to make all the components along a row and make one formula to make use of the components.
    Then, one by one, I replace the cell references with the contents of the referenced cell (without the = sign of course). This way I have developed formulae which have been virtually impossible to comprehend manually. Give it a go, you never know it may help.

    Good luck

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

    Default

      
    ohhhh, yeah that would help. Would you be able to give me an example of the setting out of that?

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
  •  

 

 
DMCA.com