Ignore #N/A Value with Vlookup???

[Parra]

I am doing a vlookup function and I sum the results. But how do I get the vlookup function to display "0" if there is no value for what it is looking for. I was trying to do a if function but it didn't work. Any suggestions. Thanks

[Aladin Akyurek]

On 2002-04-24 16:21, Parra wrote:
I am doing a vlookup function and I sum the results. But how do I get the vlookup function to display "0" if there is no value for what it is looking for. I was trying to do a if function but it didn't work. Any suggestions. Thanks

Keep #N/A and use

=SUMIF(range,"<>#N/A")

or, use:

=IF(COUNTIF(B2:B100,A1),VLOOKUP(A1,B2:G100,2),0)

to return 0 instead of #N/A when appropriate.

Aladin

[Yogi Anand]

On 2002-04-24 16:21, Parra wrote:
I am doing a vlookup function and I sum the results. But how do I get the vlookup function to display "0" if there is no value for what it is looking for. I was trying to do a if function but it didn't work. Any suggestions. Thanks

Use the following

=IF(ISNA(your_vlookup_formula),0,(your_vlookup_formula))

HTH


_________________
Yogi Anand
Edit: Deleted reference to inactive web site from signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 18:34 ]

[Mark W.]

Or, have an additional column containing the formula, =IF(ISNA(A1),0,A1), where A1 contains your VLOOKUP. You may choose to hide column A so that viewers of the worksheet are oblivious to the presence of #N/A errors. I believe this is preferrable to a formula that performs your VLOOKUP (or otherwise examines your table array) twice!

[ This Message was edited by: Mark W. on 2002-04-24 16:50 ]

[Aladin Akyurek]

On 2002-04-24 16:31, Mark W. wrote:
Or, have and additional column containing the formula, =IF(ISNA(A1),0,A1), where A1 contains your VLOOKUP. You may choose to hide column A so that viewers of the worksheet are oblivious the the presence of #N/A errors.

I bet COUNTIF will be as fast as that & will not waste space/memory.

[Mark W.]

On 2002-04-24 16:34, Aladin Akyurek wrote:

I bet COUNTIF will be as fast as that & will not waste space/memory.

Time vs. Size -- the classic worksheet design tradeoff! It all depends on the size of the table array and the average successful hit rate. COUNTIFs can be painfully slow.

My preferred design is not to perform an exact match lookup. Retrieve the results and compare it against the original lookup value. This can be quite a time saver if the hit rate is low.

[ This Message was edited by: Mark W. on 2002-04-24 16:41 ]

[Aladin Akyurek]

On 2002-04-24 16:36, Mark W. wrote:

Time vs. Size -- the classic worksheet design tradeoff! It all depends on the size of the table array and the average successful hit rate.

My fear is that you're not trading off size/memory against time: you're spending memory.

[Parra]

I would to use Yogi's formula because I feel I won't have to do much but I get an error message, any suggestions?

Thanks

[Parra]

Maybe it will help if I put the vlookup formula.

=VLOOKUP(B8,display2002,2,FALSE)

[Aladin Akyurek]

On 2002-04-24 16:44, Parra wrote:
I would to use Yogi's formula because I feel I won't have to do much but I get an error message, any suggestions?

Thanks

Give a try to the COUNTIF version too.