Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: lookup easy one

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =VLOOKUP(IF(ISNUMBER((E15)*1),(E15)*1,(E15)),pax,12,FALSE)+VLOOKUP(IF(ISNUMBER((E16)*1),(E16)*1,(E16)),pax,12,FALSE)

    The formula takes cells E15-E21 just not shown here for simplicity.

    What can I add to the formula so if cells 16-21 are blank it won't return a #N/A?

    thanks

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try,

    =IF(COUNTBLANK(E16:E21)=5,0,your mega formula)

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is zero in the domain of the left-most column of your table_array (pax)? If not, just add a row in your table_array for 0 and the value you'd rather have your VLOOKUP return.

    [ This Message was edited by: Mark W. on 2002-04-25 16:20 ]

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    just blanks in the column sometimes and numbers at other times.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =isna(VLOOKUP(IF(ISNUMBER((E15)*1),(E15)*1,(E15)),pax,12,FALSE)+VLOOKUP(IF(ISNUMBER((E16)*1),(E16)*1,(E16)),pax,12,FALSE )),0

    i think



    [ This Message was edited by: Qroozn on 2002-04-25 16:22 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jay, I tried it and it didn't work maybe more than 7 nested if's casued the problem.

    Thanks

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-25 16:20, lars wrote:
    just blanks in the column sometimes and numbers at other times.
    If 'pax' contained...

    {1,"one";2,"two";3,"three"}

    ...and you'd rather not have =VLOOKUP(A1,pax,2,0) return #N/A when cell A1 is blank then change 'pax' to...

    {0,"zero";1,"one";2,"two";3,"three"}

    ...and then =VLOOKUP(A1,pax,2,0) will return "zero" instead.

    You "build" the "smarts" into the table rather than the formula. Get it?


    [ This Message was edited by: Mark W. on 2002-04-25 16:25 ]

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Qroonz, didn't work either, not sure what's happening

    Thanks

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use the isna formula on the cells 16-21. then the sum calc will read the NA's as 0'.

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    See my reply posting above.

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
  •