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

 

[Yogi Anand]

How about posting the formula that is giving you an error!

 

[Mark W.]

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

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

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

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.

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.

It's all a matter of right-sizing your application. If one is performing that many lookups perhaps it's time to consider a database. Excel's Get External Data capabilities are extraordinary!
This message was edited by Mark W. on 2002-04-24 16:52

 

[Parra]

Here's the formula

=IF(ISNA(B8,display2002,2,FALSE),0,(B8,display2002,2,FALSE))

and I'll try the count if too. Thanks

 

[Yogi Anand]

Mark:
I think you are absolutely right! For an average Excel user, I don't believe the memory or the size are the issue in most cases -- it is the ease of use, and understanding of what is going on is perhaps more important to the average user. Talking about the use by experts for sophisticated memory intensive, calc-intensive applications is different.

Regards!

Yogi Anand

 

[Parra]

I agree, in the words of my boss "Get it Done".

I use excel everyday, but I am not a pro like most of you. So I guess I am average and as long as the formulas and macros get the job done, my boss is happy and so am I.

 

[Yogi Anand]

On 2002-04-24 16:48, Parra wrote:
Here's the formula

=IF(ISNA(B8,display2002,2,FALSE),0,(B8,display2002,2,FALSE))

and I'll try the count if too. Thanks

Your structure of the formula incorporating the use of ISNA function is right, you may want to look into your_vlookup_formula.

 

[Parra]

Thanks for the help Yogi, I have to run of to school, I try it tomorrow.

Thanks to everyone else for your help.

Parra

 

[Aladin Akyurek]

On 2002-04-24 16:48, Parra wrote:
Here's the formula

=IF(ISNA(B8,display2002,2,FALSE),0,(B8,display2002,2,FALSE))

and I'll try the count if too. Thanks

I think you forgat the VLOOKUPs in the formula. I assume display2002 is your lookup table. In order to use the COUNTIF version, you need to refer to its first column. Lets say that display2002 refers D2:E400 -- the first column range is then D2:D400. Following the example, you'd have

=IF(COUNTIF(D2:D400,B8),VLOOKUP(B8,display2000,2,0),0)

Note. 0 means the same thing to VLOOKUP as FALSE.

I don't like insisting but the above is much more efficient than

=IF(ISNA(VLOOKUP(B8,display2000,2,0)),0,VLOOKUP(B8,display2000,2,0))

Aladin

 

[Yogi Anand]

On 2002-04-24 16:48, Parra wrote:
Here's the formula

=IF(ISNA(B8,display2002,2,FALSE),0,(B8,display2002,2,FALSE))

and I'll try the count if too. Thanks

Hi Aladin:
You are right, he is missing the VLOOKUP in both instances ... so the formula should read ...

=IF(ISNA(VLOOKUP(B8,display2002,2,FALSE),0,VLOOKUP(B8,display2002,2,FALSE))

 

[Parra]

Thank you to everyone that helped me with this. I guess I was looking to do it a certain way when there were easier ways to do it. I ended up doing a SUMIF functions.

Aladin, Yogi Thanks