Help!

VLOOKUP - return 0 instead of "#N/A"

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Worksheet Functions RSS
Next:  linking points  
Author Message
EDCNB
External


Since: Sep 14, 2006
Posts: 1



PostPosted: Thu Sep 14, 2006 9:58 pm    Post subject: VLOOKUP - return 0 instead of "#N/A"
Archived from groups: microsoft>public>excel>worksheet>functions (more info?)

When using VLOOKUP to find an exact match for a value, and if there is no
exact match, the formula will return "#N/A". How should I modify the formula
to make it return the number 0 instead?

Thanks
Back to top
JLatham
External


Since: Jul 08, 2006
Posts: 511



PostPosted: Thu Sep 14, 2006 10:11 pm    Post subject: RE: VLOOKUP - return 0 instead of "#N/A" [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Nest it with an IF with a test for the #NA condition like this
=IF(ISNA(VLOOKUP(A1,range,col,param),0,VLOOKUP(A1,range,col,param))
What that says is test if the lookup will cause #NA, and if it will then
display 0 (zero) else go ahead and perform the VLOOKUP for real and display
its result.

The zero doesn't even have to be a zero, in other conditions you could put a
custom phrase there such as ,"No Match Found",

"EDCNB" wrote:

> When using VLOOKUP to find an exact match for a value, and if there is no
> exact match, the formula will return "#N/A". How should I modify the formula
> to make it return the number 0 instead?
>
> Thanks
Back to top
Dave Peterson
External


Since: Jul 08, 2005
Posts: 16264



PostPosted: Fri Sep 15, 2006 12:09 am    Post subject: Re: VLOOKUP - return 0 instead of "#N/A" [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

=if(iserror(vlookup(...)),0,vlookup(...))

In xl2007:

=iferror(vlookup(...),0)

EDCNB wrote:
>
> When using VLOOKUP to find an exact match for a value, and if there is no
> exact match, the formula will return "#N/A". How should I modify the formula
> to make it return the number 0 instead?
>
> Thanks

--

Dave Peterson
Back to top
Dave F
External


Since: Aug 04, 2006
Posts: 1791



PostPosted: Fri Sep 15, 2006 1:06 am    Post subject: RE: VLOOKUP - return 0 instead of "#N/A" [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

=IF(ISERROR(VLOOKUP([your criteria])),0,VLOOKUP([your criteria]))

"IF the VLOOKUP returns an error, THEN 0, ELSE do the VLOOKUP."

Dave
--
Brevity is the soul of wit.


"EDCNB" wrote:

> When using VLOOKUP to find an exact match for a value, and if there is no
> exact match, the formula will return "#N/A". How should I modify the formula
> to make it return the number 0 instead?
>
> Thanks
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Worksheet Functions All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum