Nested If anf If Error functions

[Mohrinho]

Hey all,

I have the following difficulty:

Please Login or Register  to view this content.

this function does not work as I want it to, which surprised me, bc in seperation both of the formulas (the IF & the IFERROR) work as desired.

With the function I refer to a cell, that either has a value (in text form), is blank or contains "-".
In the two later cases I want the cell to be blank, in the first I want the value as a number.

Looking forward to your smart solutions,
Laurents

[Richard Buttrey]

Would you upload the workbook.

I cannot see an OR condition which would be necessary if you want a blank cell given either "-" or blank.

[Mohrinho]

Ok here is a part of the workbook.
In column C everything works perfect, in column D I want him to keep the formula but put blank instead of 0. In D34 I put in an iferror function that works for this cell, buyt if i drag it to the left, I get an error message instead of the desired 119.

Thank you!

[Mohrinho]

I don't want the cell to be - or blank. I want it to be blank if the reference cell is either one of those.

[Mohrinho]

no one? ....

[Jonmo1]

You have to test for the error FIRST.
Then test for =0

Try

=IFERROR(IF(VALUE(VLOOKUP("Emisiones CO2 combinado (Gr. 2)";$1:$1048576;COLUMN(P:P);))=0;"";VALUE(VLOOKUP("Emisiones CO2 combinado (Gr. 2)";$1:$1048576;COLUMN(P:P);)));"")

[seasider89]

When I drag the formula from D34 to C34 it returns 119 like you said you wanted it to?

[Richard Buttrey]

I must admit I'm puzzled to understand your ultimate aim.

I'm also puzzled by your VLOOKUP formula. It seems to be looking up itself but without triggering a circular ref and it's not clear where the value 119 comes from. Are you able to clarify?