Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Code doesnt 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(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),)

    When the ISNUMBER(MATCH) function returns true, it gives me #N/A results in the cells which should have 1 or 0 in them.

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

    Default

    On 2002-04-14 02:20, KnAsTa wrote:
    =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),)

    When the ISNUMBER(MATCH) function returns true, it gives me #N/A results in the cells which should have 1 or 0 in them.
    Just curious: Why do you need to restart another thread on the same question?

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

    Default

    Its a different question, the formula is different now, its shortenned and For column B not A. Sorry.

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

    Default

    On 2002-04-14 02:27, KnAsTa wrote:
    Its a different question, the formula is different now, its shortenned and For column B not A. Sorry.
    Not a good reason. If still interested, try to provide the data using the procedure I described to you in the previous post.

  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, next time i'll contain it to one thread. Sorry.

    But my question is, in the previous 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),)

    This works fine for me.

    but if u change the Range in the match function to $B$1:$B$10 it doesnt work, it gives me #N/A results.

    Here is my new formula:

    =IF(ISNUMBER(MATCH(A5,week1.xls!$B$1:$B$10,0)),IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)
    Very sorry for the inconvenience. And thankyou for your constant help.

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

    Default

    Could someone PLEASE help me out on the above question. Its for an assignment, and its due quite soon. It would be very much appreciated. Thanks!

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    >Could someone PLEASE help me out on the >above question. Its for an assignment, and >its due quite soon. It would be very much >appreciated. Thanks!

    You have asked the same question many times.

    Suggestions:
    - read the information in Excel's Help
    - try the suggestions that people provided
    - provide feedback on what worked and what did not work
    - Use Search and find all postings to your question and also try search for other relevant subjects

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

    Default

    Yes, i have asked similar questions every time, but each time i post it is a different formula, i keep getting stuck on different parts of the formula. If someone can explain what is wrong with this formula above, why it works for A and not B, then that is all i need. This is the last part of this formula i need to know.

    I have read all through the excel help pages concering the functions MATCH ISNA and the LOOKUP function, and have also searched through the majority of the LOOKUP questions on this site.

    I am sorry for posting so many times on similar formulae, i am just having a lot of trouble understanding it.

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    >but if u change the Range in the match >function to $B$1:$B$10 it doesnt work, it >gives me #N/A results.

    >Here is my new formula:

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

    Is the information in A5 in the Range B1:B10?
    Your previous formula looked in A1:A10.


    To access from multiple external files, consider
    a)
    hardcode the full address

    =IF(OR(A38="",COUNTIF('E:Data02Tech02[LookupD1.xls]1'!$A$2:$F$6,A38)=0),"",VLOOKUP(A38,'E:Data02Tech02[LookupD1.xls]1'!$A$2:$F$6,6,0))

    or b) if the other files are OPEN

    Put reference in cell (example C37 has LookupD1.xls!rL2_ )

    =IF(OR(A37="",COUNTIF(INDIRECT(C37),A37)=0),"",VLOOKUP(A37,INDIRECT(C37),6,0))

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

    Default

    >Is the information in A5 in the Range >B1:B10?
    >Your previous formula looked in A1:A10.

    The information in A5 is in the range $B$1:$B$10 which is the external spreadsheet. However it does not return my values for the true statement.

    I have tried to hardcode in the address' of the external files but it is still the same. When i pasted the formulae into this forum, i had my external file open with the main spreadsheet, so it did not need a hardcoded addreess.


    My external spreadsheet is this(tab delimited):

    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

    And i am VLOOKING up Leeds which is A5 in my main spreadsheet. This external file contains 20 english soccer teams, playing 10 games with the home and away scores.

    The formula is determining if the away team wins or loses. Ie puts a 1 for a win, or a 0 for a loss.

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
  •