=VLOOKUP validity

[Greg H]

=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!

 

[Corey]

=IF(VLOOKUP(B16,Quote!A:H,8,FALSE)<>"",VLOOKUP(B16,Quote!A:H,8,FALSE),0)

 

[Roger Govier]

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)

 

[rmd251073]

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,VLOOKUP(B16,Quote!A:H,8,FALSE))

Hope this will be useful to you.

All the best.

Rakesh Darji