Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Help...............again

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

    Default

    Ok, i have this formula, it takes data from external spreadsheets and records it in the main spreadsheet containing A5 (see below).

    =IF(ISNUMBER(MATCH(A5,week1.xls!$A$1:$A$10,0)),
    -- this part works, it returns true or false. Tested it.

    IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)>VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,0),
    -- this part works, it returns 1 or 0 Iff first lookup greater than second lookup.

    IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0) -- this last part does not work, and for the cells it is meant to put 1 or 0 in, it puts #N/A.

    Why does this happen when we get true or false returning, and the last piece of code is exactly the same as the first piece of code, just with the > sign changed to <.

    Complete code:
    =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), IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)
    All the ranges are correct also.

    Any help would be greatly appreciated. Thanx

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    try this - it worked for me. i think you had a missing bracket and no false statement (ie. "0")

    =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),IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0),0))

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

    Default

    It still didnt work, it still gives me #N/A values. I really don't get it.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,632
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Go to that "external spreadsheet", locate A1:D10, activate an empty cell, type =, select A1:D10, hit F9, copy what you see after the =-sign, and paste it in the follow up post.

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
  •