A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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



 
 
Thread Tools Display Modes
  #1  
Old September 15th 06, 01:58 AM posted to microsoft.public.excel.worksheet.functions
EDCNB
external usenet poster
 
Posts: 2
Default VLOOKUP - return 0 instead of "#N/A"

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
Ads
  #2  
Old September 15th 06, 02:09 AM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 35,220
Default VLOOKUP - return 0 instead of "#N/A"

=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
  #3  
Old September 15th 06, 02:11 AM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 2,204
Default VLOOKUP - return 0 instead of "#N/A"

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

  #4  
Old September 15th 06, 05:06 AM posted to microsoft.public.excel.worksheet.functions
Dave F
external usenet poster
 
Posts: 2,574
Default VLOOKUP - return 0 instead of "#N/A"

=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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup, return zero if not found molsansk Excel Worksheet Functions 2 August 22nd 06 06:40 PM
vlookup function return all values j2thea Excel Worksheet Functions 20 November 2nd 05 10:32 PM
Using a Vlookup to return values in a data list? rtjeter Excel Worksheet Functions 2 April 26th 05 05:56 AM
Want VLookup to Return the row above JoOwl0 Excel Worksheet Functions 8 April 23rd 05 07:16 PM
Vlookup of an if statement return James Excel Worksheet Functions 2 April 6th 05 10:28 PM


All times are GMT +1. The time now is 05:02 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.