Need Vlookup to return a value of $0.00 in blank cells

G

Guest

I using this formula

=IF(ISNA(VLOOKUP(A7,$O$2:$P$39,2,0)),"",VLOOKUP(A7,O$2:$P$39,2,FALSE))

I need the department that do not have a dollar value to equal to $0.00 in
my list on matched up department sales Or my %of Sales formula does not work.


Department Sales Departement Sales % of

Sales
Baseball $186.95 Baseball $186.95 #VALUE!
Bikes $3,317.88 Basketball #VALUE!
Exersice $14.98 Bikes $3,317.88 #N/A
Footwear $217.37 Billards #VALUE!
General $35.99 Bowling #VALUE!
Golf $210.83 Darts #VALUE!
Hockey $906.92 Exersice $14.98 #N/A
Inlines $249.98 Figure Skates #VALUE!
Licenced $79.99 FootBall #VALUE!
Racquets $112.75 Footwear $217.37 #N/A
Rental $134.99 General $35.99 #N/A
Repairs $57.73 Gift Cert #VALUE!
Skateboards $201.97 Golf 210.83
 
G

Guest

Untested, but think we could try ISERROR and change the: "" to a zero, viz.:
=IF(ISERROR(VLOOKUP(A7,$O$2:$P$39,2,0)),0,VLOOKUP(A7,O$2:$P$39,2,FALSE))
 
R

Roger Govier

Hi

Rather than the double Vlookup, you could test for the existence of the
Department first with Match

=IF(ISNUMBER(MATCH(A7,$O$2:$P$39,0)),VLOOKUP(A7,O$2:$P$39,2,FALSE),0)
 
Top