=VLOOKUP validity
 

=VLOOKUP(B16,Quote!A:H,8,FALSE)
In this formula, what must I add to have the result return a 0 (zero) if the
lookup does not find the value in the lookup range. I can't have a N/A value?
I guess I need to use an =IF statement but cannot get it right.

Something like =IF(VLOOKUP(B16,Quote!A:H,8,FALSE) ##is found, then##
VLOOKUP(B16,Quote!A:H,8,FALSE) ##else value = 0##

Thanks in advance!

=VLOOKUP validity
 

=IF(VLOOKUP(B16,Quote!A:H,8,FALSE)<"",VLOOKUP(B16 ,Quote!A:H,8,FALSE),0)
"Greg H" <Greg wrote in message
...
=VLOOKUP(B16,Quote!A:H,8,FALSE)
In this formula, what must I add to have the result return a 0 (zero) if
the
lookup does not find the value in the lookup range. I can't have a N/A
value?
I guess I need to use an =IF statement but cannot get it right.

Something like =IF(VLOOKUP(B16,Quote!A:H,8,FALSE) ##is found, then##
VLOOKUP(B16,Quote!A:H,8,FALSE) ##else value = 0##

Thanks in advance!

=VLOOKUP validity
 

Hi Greg

Rather than using 2 Vlookups which are expensive on processing time, use
a Countif to see if the value from B16 exists in column A of the lookup
table first.

=IF(COUNTIF(Quote!A:A,B16),VLOOKUP(B16,Quote!A:H,8 ,FALSE),0)


--
Regards

Roger Govier


"Greg H" <Greg wrote in message
...
=VLOOKUP(B16,Quote!A:H,8,FALSE)
In this formula, what must I add to have the result return a 0 (zero)
if the
lookup does not find the value in the lookup range. I can't have a N/A
value?
I guess I need to use an =IF statement but cannot get it right.

Something like =IF(VLOOKUP(B16,Quote!A:H,8,FALSE) ##is found, then##
VLOOKUP(B16,Quote!A:H,8,FALSE) ##else value = 0##

Thanks in advance!

=VLOOKUP validity
 

the best way to get rid of this problem is use formula as given below:

=IF(ISERROR(VLOOKUP(B16,Quote!A:H,8,FALSE),0,VLOOK UP(B16,Quote!A:H,8,FALSE))

Hope this will be useful to you.

All the best.

Rakesh Darji



Greg H wrote:
=VLOOKUP(B16,Quote!A:H,8,FALSE)
In this formula, what must I add to have the result return a 0 (zero) if the
lookup does not find the value in the lookup range. I can't have a N/A value?
I guess I need to use an =IF statement but cannot get it right.

Something like =IF(VLOOKUP(B16,Quote!A:H,8,FALSE) ##is found, then##
VLOOKUP(B16,Quote!A:H,8,FALSE) ##else value = 0##

Thanks in advance!