+ Reply to Thread
Results 1 to 4 of 4

Help with adding ISBLANK to a VLOOKUP function

  1. #1
    Registered User
    Join Date
    05-23-2014
    Location
    Maine, USA
    MS-Off Ver
    2010
    Posts
    3

    Help with adding ISBLANK to a VLOOKUP function

    I have several spots in a spreadsheet that are utilizing VLOOKUP and ISNA so that if a sample number matches a corresponding value is returned and if there is no sample number that matches it returns -99 which indicates there is no value available. Below is an example of this formula.
    =IF(ISNA(VLOOKUP($I2,'PIC SW Corr'!$A$2:$W$801,23,FALSE)),-99,VLOOKUP($I2,'PIC SW Corr'!$A$2:$W$801,23,FALSE))
    The formula works well except for one thing. When a sample number exists on the other page, but a cell is empty (usually because a value will be added at a later date) it shows up as a 0 which of course messes up graphing. I was able to change the cell format where the formula is to make these problem cells show up blank, but what I really need is to have it show -99 indicating there is no value available. I suspect that I need to use ISBLANK function but am struggling with how to incorporate that into an already complex formula.
    Any suggestions?

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Help with adding ISBLANK to a VLOOKUP function

    Is the cell really blank or is a formula returning "" which will not register as TRUE when using ISBLANK formula. Will there be cases where it will legitimately be 0 that it is pulling back?

    Assuming it will never be zero, try
    IF(IFERROR(VLOOKUP($I2, 'PIC SW Corr'!$A$2:$W$801,23,FALSE),-99)=0, -99, IFERROR(VLOOKUP($I2, 'PIC SW Corr'!$A$2:$W$801,23,FALSE),-99))
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    05-23-2014
    Location
    Maine, USA
    MS-Off Ver
    2010
    Posts
    3

    Re: Help with adding ISBLANK to a VLOOKUP function

    There are times when the value will actually be 0. The cell is really blank (I've even double checked by clearing cell contents). I'm not sure I understand your first question though.

  4. #4
    Registered User
    Join Date
    05-23-2014
    Location
    Maine, USA
    MS-Off Ver
    2010
    Posts
    3

    Re: Help with adding ISBLANK to a VLOOKUP function

    I think it might be working using this formula:
    =IF(ISNA(VLOOKUP($I2,'PIC SW Corr'!$A$2:$W$801,23,FALSE)),-99,IF(VLOOKUP($I2,'PIC SW Corr'!$A$2:$W$801,23,FALSE)="",-99,VLOOKUP($I2,'PIC SW Corr'!$A$2:$W$801,23,FALSE)))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Complicated VLookup Function with =IF(AND(NOT(ISBLANK
    By chrishull1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-09-2014, 01:31 PM
  2. Adding Conditional Formatting to =if(isblank(2E),1,0)
    By amyflowersteam in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-27-2013, 01:26 PM
  3. Help with ISBLANK function referring to a cell with another ISBLANK formula
    By camdameron in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-23-2011, 12:45 PM
  4. IF(ISBlank with Vlookup
    By amthyst826 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-23-2010, 01:07 PM
  5. Adding A Not(IsBlank) To IF Statement(help)
    By getmhawks in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-12-2006, 05:50 PM

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