formula results and custom number formats

[miconian]

I've got a cell with the following custom number formatting:

_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

As you can see, a zero should show up as a dash with a dollar sign. Usually, with this formatting, it does.

However, this particular cell also contains this formula:

=IF(ISNA(VLOOKUP(A9,'2011 Calc (Reset)'!$H$44:$M$54,6,FALSE) = TRUE),"0",VLOOKUP(A9,'2011 Calc (Reset)'!$H$44:$M$54,6,FALSE))

Basically, if the result of the VLOOKUP is NA, the result of the overall formula should be zero.

However, when this formula gives a zero result, all the formatting goes out the window. The user sees a zero flush against the left wall of the cell.

What can I do so that, when the formula returns zero, that zero is formatted properly?

Excel 2007, Windows XP

 

[Joe4]

When you place double-quotes around a number, it returns it as text, not numeric (double-quotes are text qualifiers). Try removing them (you don't need the "=TRUE" part either), i.e.:

=IF(ISNA(VLOOKUP(A9,'2011 Calc (Reset)'!$H$44:$M$54,6,FALSE)),0,VLOOKUP(A9,'2011 Calc (Reset)'!$H$44:$M$54,6,FALSE))

 

[miconian]

Thanks, man. I can't believe I missed that quote issue.

As a special extra credit bonus: Do you happen to see a more efficient way to write that formula in general? I know VLOOKUPs slow down workbooks, and this one is slow, and there are, as you can see, two VLOOKUPS in this cell. And, of course, this cell actually represents hundreds of similar cells.

 

[Joe4]

Are you using Excel 2007 or later?
If so, you can get away with one VLOOKUP if you make use of the new IFERROR function.

=IFERROR(VLOOKUP(...),0)

 

[miconian]

Yes. Very nice, thanks.