Handling Dashes in IF statement

[Ant]

Hello,

I have a vlookup statement that, when the table is empty, it returns a -
(dash).

Dependant upon that vlookup cell, I have the following IF statement
=IF(B51="",NA(),B57)

The IF statement does not work because of the dashes, but I have tried other
alternatives in the "" to recognise the dashes, but none work.

How do I modify the IF statement to recognise the dash as "no entry" and
return the N/A. Once the N/A is returned, a conditional format changes the
font colour to the same as the background. I am using the N/A to avoid my
graph showing the line at zero.

Thanks in advance.
Ant

 

[Fred Smith]

From what you've told us, it's as simple as checking for a dash, as in:
=IF(B51="-",NA(),B57)

The next question is: why have the Vlookup return a dash? Why not have it
return #N/A? That would save you a step.

Finally, is it really returning a dash, or is it something formatted as a
dash? Formatting won't change the underlying value. In your If statement,
you need to ignore formatting, and check for the value actually being
returned.

Regards,
Fred

 

[Ant]

Hi Fred,

Your first suggestion I have already attempted and it did not work. VLookup
is returning a dash for a 0 value in the lookup table, but i don't know what
the underlying value is to place into the If statement.

I am not asking Vlookup to return a dash. I have changed the format to
return a zero or the N/A, but it does not solve the problem

Yes, you hit the nail on the head. That is what I am asking.... What is the
actual value being returned so that way I can place it in my If statement. Or
is there another method to do this?

Ant

 

[David Biddulph]

It sounds as if the nitty-gritty is where Fred said:
"> Finally, is it really returning a dash, or is it something formatted as a

dash? Formatting won't change the underlying value. In your If statement,
you need to ignore formatting, and check for the value actually being
returned."

It sounds as if you are returning a zero and in the relevant cell you are
formatting it as a dash.
If so, your IF statement should be =IF(B51=0,NA(),B57)

 

[Ant]

Thanks David and Fred.

It was a Zero. I think when I tried the zero, I must have left the inverted
commas around it.

Regards
Ant