"Stephen" <none wrote...
Then cell A1 doesn't contain the numerical value 0. It may be text
that looks like 0, or something else, but it isn't the numerical
value 0.
....

Picky: given all the other messages in this thread, it's nearly
certain the OP's A1 cell contains text rather than a numeric 0 or
nothing at all. However, if the cell contained a very small negative
numeric value, so small that it would display as 0, that would also
cause the LOOKUP formula to return #N/A.