Ignore #N/A Value with Vlookup???

Parra

Well-known Member
Joined
Feb 21, 2002
Messages
752
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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. 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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Maybe it will help if I put the vlookup formula.

=VLOOKUP(B8,display2002,2,FALSE)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top