+ Reply to Thread
Results 1 to 3 of 3

PLEASE HELP: ISERROR, ISBLANK with LOOKUP

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    PLEASE HELP: ISERROR, ISBLANK with LOOKUP

    I am having trouble trying to get ISERROR and ISBLANK to work together in one formula while using LOOKUP with times for a coaching scale. I have some months of data that have not populated, so until I have data collected for those months, I want those cells to remain empty in my Pending Times Score section. I am using the ISERROR formula because I have people who have times lower than 10:00:00, but I get an #N/A unless I use the first formula below. Attached is my sample worksheet.

    This formula gives me that I want when I have data in the Pending Times section of my worksheet, but will give all 4's for months when I have no data.
    =IF(ISERROR(LOOKUP(B3,Pending_Scale)),4,LOOKUP(B3,Pending_Scale))

    This formula will work for the cells that have no data, but will give me a blank when the individual should be getting a score of 4.00

    =IF(ISERROR(LOOKUP(H3,PendingScale)),IF(ISBLANK(LOOKUP(H3,PendingScale)),4,""),LOOKUP(H3,PendingScale))
    Attached Files Attached Files

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: PLEASE HELP: ISERROR, ISBLANK with LOOKUP

    =if(B3="","",IF(ISERROR(LOOKUP(B3,Pending_Scale)),4,LOOKUP(B3,Pending_Scale)))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: PLEASE HELP: ISERROR, ISBLANK with LOOKUP

    Quote Originally Posted by NoJokenVA View Post
    =IF(ISERROR(LOOKUP(B3,Pending_Scale)),4,LOOKUP(B3,Pending_Scale))
    Maybe this...

    =IFERROR(IF(B3="","",LOOKUP(B3,Pending_Scale)),4)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1