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.

 VLOOKUP - return 0 instead of "#N/A"
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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

#1
September 15th 06, 01:58 AM posted to microsoft.public.excel.worksheet.functions
 EDCNB external usenet poster Posts: 2
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
#2
September 15th 06, 02:09 AM posted to microsoft.public.excel.worksheet.functions
 Dave Peterson external usenet poster Posts: 35,220
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
September 15th 06, 02:11 AM posted to microsoft.public.excel.worksheet.functions
 JLatham external usenet poster Posts: 2,204
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
September 15th 06, 05:06 AM posted to microsoft.public.excel.worksheet.functions
 Dave F external usenet poster Posts: 2,574
VLOOKUP - return 0 instead of "#N/A"

"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 Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 12:25 AM.