Help with #N/A result

[easybpw]

Hello and thanks in advance for your help. I'm using the below formula and it works as designed. However there are now times when the result of the formula will return a #N/A, which I don't want. My preferred result when this happens is it returns a -0-. I'm stumped on how to do it. I've tried adding an iferror statement but that doesn't work with the other parts of the formula. It does work when I am only using that as the formula. Hope that made sense. Below is what I have.

=IF(VLOOKUP("CRC",Adjustments!$I$4:$J$12,2,0)>0," ",-VLOOKUP("CRC",Adjustments!$I$4:$J$12,2,0))

 

[Joe4]

Are you doing this in Excel 2013 or 365 (you have both listed)?
Excel 365 has some new functions which makes things like this easier.

 

[easybpw]

Are you doing this in Excel 2013 or 365 (you have both listed)?
Excel 365 has some new functions which makes things like this easier.

Sorry, I use both, depending on if I'm at work or home. This is in 365.

 

[Joe4]

Excellent. Then we can take advantage of the new LET function.
See if this does what you want:

=LET(x,VLOOKUP("CRC",Adjustments!$I$4:$J$12,2,0),IF(ISERROR(x),"-0-",IF(x>0," ",-x)))

 

[easybpw]

Excellent. Then we can take advantage of the new LET function.
See if this does what you want:

=LET(x,VLOOKUP("CRC",Adjustments!$I$4:$J$12,2,0),IF(ISERROR(x),"-0-",IF(x>0," ",-x)))

Yes, works perfect. I should have clarified that -0- I really wanted $0.00 but that was an easy change for me. Thanks again.

 

[Joe4]

You are welcome.
Glad I was able to help!

Note that new LET function is really handy. With it, you don't need to repeat the same long formula in the same expression.

LET function - Microsoft Support

support.microsoft.com