VLOOKUP
 

I have this function which works.

=VLOOKUP(A3,NewRev!$A$1:$B$286,2,FALSE) for A3-A500ish

However, if what is in A3 doesn't return a result from the NewRev worksheet,
I get #N/A, my question is, instead of an #N/A result, can I get blank or 0
so that when the result is used in a Sum formula I dont get a #N/A, or do I
have to go through and manually delete anything with #N/A as a result?

TYIA, Brandon

VLOOKUP
 

"Brandon" wrote:

I have this function which works.

=VLOOKUP(A3,NewRev!$A$1:$B$286,2,FALSE) for A3-A500ish

However, if what is in A3 doesn't return a result from the NewRev worksheet,
I get #N/A, my question is, instead of an #N/A result, can I get blank or 0
so that when the result is used in a Sum formula I dont get a #N/A, or do I
have to go through and manually delete anything with #N/A as a result?

TYIA, Brandon


Also, if what is in A3 returns multiple results, is there a way to sum the
total results?

VLOOKUP
 

Sometimes you want the errors to appear.

You can sum while ignoring the errors using a formula like this:

=SUMIF(A1:A10,"<1E100")

However, if you don't want the errors to appear try this to return 0:

=IF(COUNTIF(NewRev!$A$1:$A$286,A3),VLOOKUP(A3,NewR ev!$A$1:$B$286,2,0),0)

--
Biff
Microsoft Excel MVP


"Brandon" wrote in message
...
I have this function which works.

=VLOOKUP(A3,NewRev!$A$1:$B$286,2,FALSE) for A3-A500ish

However, if what is in A3 doesn't return a result from the NewRev
worksheet,
I get #N/A, my question is, instead of an #N/A result, can I get blank or
0
so that when the result is used in a Sum formula I dont get a #N/A, or do
I
have to go through and manually delete anything with #N/A as a result?

TYIA, Brandon

VLOOKUP
 

Try this...

=SUMIF(NewRev!$A$1:$A$286,A3,NewRev!$B$1:$B$286)

--
Biff
Microsoft Excel MVP


"Brandon" wrote in message
...


"Brandon" wrote:

I have this function which works.

=VLOOKUP(A3,NewRev!$A$1:$B$286,2,FALSE) for A3-A500ish

However, if what is in A3 doesn't return a result from the NewRev
worksheet,
I get #N/A, my question is, instead of an #N/A result, can I get blank or
0
so that when the result is used in a Sum formula I dont get a #N/A, or do
I
have to go through and manually delete anything with #N/A as a result?

TYIA, Brandon


Also, if what is in A3 returns multiple results, is there a way to sum the
total results?