Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 53
Default Formula have a VLOOKUP but want it to =0 if no value is found

Hi,
I have entered a VLOOKUP to search an Item no. and return the amount in sold
in the month. The VLOOKUP returns the closest value from the nearest item no.

Should I use an IF function so that it will only put in a value if the item
number matches exactly?

Can anyone help?

Thank you in advance,
Nicole
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Formula have a VLOOKUP but want it to =0 if no value is found

First, set the range_lookup argument to 0 or FALSE. This will ensure that
*only* exact matches are found.

=VLOOKUP("A",X:Y,2,0)

Then, you'll have to add an error trap to the formula. Since you didn't post
your formula this is a generic method of error trapping.

=IF(ISNA(your_formula_here),0,your_formula_here)

--
Biff
Microsoft Excel MVP


"Nicole" wrote in message
...
Hi,
I have entered a VLOOKUP to search an Item no. and return the amount in
sold
in the month. The VLOOKUP returns the closest value from the nearest item
no.

Should I use an IF function so that it will only put in a value if the
item
number matches exactly?

Can anyone help?

Thank you in advance,
Nicole



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 53
Default Formula have a VLOOKUP but want it to =0 if no value is found

Thank you, this has worked.

Kind Regards,
Nicole Clayfield

"T. Valko" wrote:

First, set the range_lookup argument to 0 or FALSE. This will ensure that
*only* exact matches are found.

=VLOOKUP("A",X:Y,2,0)

Then, you'll have to add an error trap to the formula. Since you didn't post
your formula this is a generic method of error trapping.

=IF(ISNA(your_formula_here),0,your_formula_here)

--
Biff
Microsoft Excel MVP


"Nicole" wrote in message
...
Hi,
I have entered a VLOOKUP to search an Item no. and return the amount in
sold
in the month. The VLOOKUP returns the closest value from the nearest item
no.

Should I use an IF function so that it will only put in a value if the
item
number matches exactly?

Can anyone help?

Thank you in advance,
Nicole



.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Formula have a VLOOKUP but want it to =0 if no value is found

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Nicole" wrote in message
...
Thank you, this has worked.

Kind Regards,
Nicole Clayfield

"T. Valko" wrote:

First, set the range_lookup argument to 0 or FALSE. This will ensure that
*only* exact matches are found.

=VLOOKUP("A",X:Y,2,0)

Then, you'll have to add an error trap to the formula. Since you didn't
post
your formula this is a generic method of error trapping.

=IF(ISNA(your_formula_here),0,your_formula_here)

--
Biff
Microsoft Excel MVP


"Nicole" wrote in message
...
Hi,
I have entered a VLOOKUP to search an Item no. and return the amount in
sold
in the month. The VLOOKUP returns the closest value from the nearest
item
no.

Should I use an IF function so that it will only put in a value if the
item
number matches exactly?

Can anyone help?

Thank you in advance,
Nicole



.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup - Last found entry within table [email protected] Excel Discussion (Misc queries) 3 September 30th 08 12:05 PM
Alternative for Vlookup output of #N/A if data not found? mcmilja Excel Discussion (Misc queries) 4 May 8th 08 07:20 PM
VLookup Value not found ? Jeff C Excel Discussion (Misc queries) 6 October 14th 07 11:56 PM
Vlookup, return zero if not found molsansk Excel Worksheet Functions 2 August 22nd 06 06:40 PM
vlookup not found BadgerDave Excel Worksheet Functions 1 April 11th 06 03:29 PM


All times are GMT +1. The time now is 11:10 PM.

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

About Us

"It's about Microsoft Excel"