View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.misc
Ken Johnson Ken Johnson is offline
external usenet poster
 
Posts: 1,073
Default Formula showing zero's

On Feb 15, 11:58*am, Mike Rogers <Mike060349@NoxSpamxAOLDOTcom wrote:
Sheeloo

Tried both of your solutions and am getting the same "0#0" as a result of my
concatenate formula. *If I could only get rid of the "0" that C20 is causing
I would be able to live with it. *My formula in C20 is now
IF(ISNA(VLOOKUP(G18,MultiCustomer,4,FALSE)),"",VLO OKUP(G18,MultiCustomer,4,FALSE)) *Any other ideas?

Mike Rogers

"Sheeloo" wrote:
You can replace C19 with IF(C19="","",C19) and similary for C20.


Or you can use ISNA in the cells having VLOOKUP like this
=IF(ISNA(Vlookup(...),"",Vlookup(...)))


If you want to supress # then you can combine it with the IF formula with
either C19 or C20....


"Mike Rogers" wrote:


I have a formula in O5 ,=CONCATENATE(K4,P10,C19,"#",C20). *This formula
returns 0#0. *K4 is empty until a macro inputs a number, P10 is empty until
the user inputs data, C19 has the formula *
=VLOOKUP(G18,MultiCustomer,3,FALSE), and C20 has the formula *
=VLOOKUP(G18,MultiCustomer,4,FALSE). *How do I change the formula in O5 to
return nothing if the two lookup formulas (C19 & C20) have not looked up the
data it is designed to. If these to cells are not showing anything they are
not still not empty because they have a formula, thus causing the concatenate
formula to return the zero’s. *Any ideas???


Mike Rogers


Sounds like the VLOOKUP is returning 0 because the table has a blank
in the cell whose value is being returned.
If this is the case then maybe...

=IF(ISBLANK(VLOOKUP(G18,MultiCustomer,3,FALSE)),"" ,VLOOKUP
(G18,MultiCustomer,3,FALSE)) in C19 and...

=IF(ISBLANK(VLOOKUP(G18,MultiCustomer,4,FALSE)),"" ,VLOOKUP
(G18,MultiCustomer,4,FALSE)) in C20.

Ken Johnson