Remember Me?

#1
September 15th 06, 01:58 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 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
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 35,218
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
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 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
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post molsansk Excel Worksheet Functions 2 August 22nd 06 06:40 PM j2thea Excel Worksheet Functions 20 November 2nd 05 11:32 PM rtjeter Excel Worksheet Functions 2 April 26th 05 05:56 AM JoOwl0 Excel Worksheet Functions 8 April 23rd 05 07:16 PM James Excel Worksheet Functions 2 April 6th 05 10:28 PM

All times are GMT +1. The time now is 08:23 AM.