VLOOKUP returning #N/A

[Michelle7890]

I am using vlook up in a list of accounts that varies from week to week. How
can I make vlookup return a 0 or blank rather than the pesky N/A when a
prticular account is not on the list for the week? The N/A is messing up my
total lines?

 

[Sheeloo]

IF(ISNA(VLOOKUP()),"",VLOOKUP())

if you have Exel 2007 then
IFERROR(VLOOKUP(),"")

Replace VLOOKUP() with your VLOOKUP formula

 

[Michelle7890]

Do I need = in there somewhere- I am getting some sort fo formula error??

 

[John C]

You could also switch to using SUMPRODUCT instead of VLOOKUPs. Then would
return 0 value instead of #N/A.
for exampl
=SUMPRODUCT(--($A$2:$A$100=accountnumber),--(MONTH($B$2:$B$100)=1),($C$2:$C$100))

This would get the dollar value in column C if your account number matches
from your account list, and the month is January from your list (I threw that
in, you can use multiple criteria with sumproduct). Note, this is only if you
are trying to retrieve numeric data.

 

[Peo Sjoblom]

This was not meant to be taken literally

use


=IF(ISNA(your_formula),"",your_formula)

--


Regards,


Peo Sjoblom

 

[Sheeloo]

Thanks Peo...