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

Thread: Problem with a formula

  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, right now this formula only works for the first half: (it is looking for several values in an external spreadsheet and returning the correct values. either 3 or 0)

    =IF(ISERROR(LOOKUP(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),3,0),

    The second part of this formula however, doesnt work. It is returning #N/A where it should be returning another set of 3's and 0's. I had some help before on this from Ian (thanks for that) but what i used from his explination helped, but it still doesnt work.

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

    So if anyone can find the problem with this code, it would be greatly appreciated. Thanks a lot

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

    Default

    With the addition of a ) at the end,
    and revision of the first part of your formula, it looks OK. The brackets are incorrect at the end of the first part.

    Revise the references to fit your information.

    =IF(ISERROR(LOOKUP(B1,$A$2:$A$10)),"",IF(VLOOKUP(B1,$A$2:$D$10,3,0)>VLOOKUP(B1,$A$2:$D$10,4,0),3,0))

    Note. With the above, if there is no value to lookup, the formula shows a blank "".

    Check each part of the formula and the data to isolate the error.

    Another approach is to add another column to the lookup table with =C3-D3

    The formula could then be

    =(VLOOKUP(B1,A1:E10,5)>0)*3

    Or put all the calculations on the Lookup Table and the formula could be

    =VLOOKUP(B1,A1:F10,6)

    <

    [ This Message was edited by: Dave Patton on 2002-04-13 07:52 ]

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
  •