Help with adding ISBLANK to a VLOOKUP function

**llubelczyk** · 05-23-2014, 01:10 PM

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?

**ChemistB** · 05-23-2014, 01:26 PM

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))

**llubelczyk** · 05-23-2014, 01:29 PM

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.

**llubelczyk** · 05-23-2014, 02:10 PM

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)))