Excluding cells with no value in calculation

[Teri]

I have to calculate a value based on a VLOOKUP, but one of the cells (outside
the VLOOKUP table) may be blank. If that cell is blank, I want zero for an
answer. My current formula is:
=(VLOOKUP(E2,$J$2:$L$6,3)+IF(ISNUMBER(C2),0.5,0))

Cell E2 is a percentage
Cell C2 is a quote number

J K
L
GP Greater Than or Equal To… But Less Than… Points
-100% 0% 0.0
0% 21% 0.5
21% 31% 1.0
31% 40% 1.5
40% 101% 2.0

 

[Peo Sjoblom]

One way

=(C2<>"")*(VLOOKUP(E2,$J$2:$L$6,3,0)+IF(ISNUMBER(C2),0.5,0))


Regards,

Peo Sjoblom

 

[Teri]

I still get #N/A as a result.

One way

=(C2<>"")*(VLOOKUP(E2,$J$2:$L$6,3,0)+IF(ISNUMBER(C2),0.5,0))


Regards,

Peo Sjoblom

 

[Harlan Grove]

Teri wrote...

I still get #N/A as a result.

"Peo Sjoblom" wrote: ....

Peo may have erred by using a 4th argument to VLOOKUP. Given the table
above, it's unlikely you want exact matching. Try

=IF(C2="","",VLOOKUP(E2,$J$2:$L$6,3)+ISNUMBER(C2),0.5,0))

 

[Peo Sjoblom]

I misunderstood, I thought that C2 was empty, so you just want to get rid of
the N/A error from the VLOOKUP
because the numeric value is not within the range,

IF(ISNA(VLOOKUP(E2,$J$2:$L$6,3)),0,VLOOKUP(E2,$J$2:$L$6,3)+IF(ISNUMBER(C2),0.5,0))

Regards,

Peo Sjoblom

 

[Teri]

Now I'm getting .5 in the cell even when there's nothing in the other cells.
This is so frustrating. Can you figure it out?

 

[Teri]

I copied this right into my spreadsheet and got an error message. Is there a
( missing somewhere?

 

[Peo Sjoblom]

Which cell is it that can be blank, if it is C2 then you can use my first
formula after removing ,0 in the VLOOKUP formula, if you want to test against
both C2 and an error from the VLOOKUP you can use


=IF(OR(C2="",ISNA(VLOOKUP(E2,$J$2:$L$6,3))),0,VLOOKUP(E2,$J$2:$L$6,3)+IF(ISNUMBER(C2),0.5,0))




Regards,

Peo Sjoblom

 

[Teri]

FANTASTIC! Thank you SO MUCH for your help!

Which cell is it that can be blank, if it is C2 then you can use my first
formula after removing ,0 in the VLOOKUP formula, if you want to test against
both C2 and an error from the VLOOKUP you can use


=IF(OR(C2="",ISNA(VLOOKUP(E2,$J$2:$L$6,3))),0,VLOOKUP(E2,$J$2:$L$6,3)+IF(ISNUMBER(C2),0.5,0))




Regards,

Peo Sjoblom

 

[Harlan Grove]

I copied this right into my spreadsheet and got an error message.
Is there a ( missing somewhere?

"Harlan Grove" wrote: ....

No, there's a missing IF call. Make that

=IF(C2="","",VLOOKUP(E2,$J$2:$L$6,3)+IF(ISNUMBER(C2),0.5,0))