vlookup - can't find match

[Tami]

if i have a vlookup and it doesn't find a match and the data is not sorted in
ascending order, how do i get it to return a 0 instead of N/A or the closest
value?

assume somthing simple like: =VLOOKUP(A4,Sheet1!A5:C21,3)

 

[Roger Govier]

Hi Tami

Add the 4th optional parameter of Vlookup of False or 0
=VLOOKUP(A4,Sheet1!A5:C21,3,0)
this will force an exact Match.

To avoid the #N/A error, when the lookup value is not found, try
=IF(COUNTIF(Sheet1!A1:A23,A4),VLOOKUP(A4,Sheet1!A5:C21,3,0),0)
or, the slightly longer
=IF(ISNA(VLOOKUP(A4,Sheet1!A5:C21,3,0)),0,VLOOKUP(A4,Sheet1!A5:C21,3,0))