I'm using some vlookup formulas to verify dates, and I want to get a "0"
response for dates that are not in my lookup list rather than "#n/a".
Any suggestions on a formula?
I tried =if(vlookup(date,A1:A500,1,0)=1,1,0)
However this still giving me a #n/a answer.

Thanks

Handle NA() using ISNA function

=IF(ISNA(lookupformula),0,lookupformula)

If this post helps click Yes
---------------
Jacob Skaria


"Brian" wrote:

I'm using some vlookup formulas to verify dates, and I want to get a "0"
response for dates that are not in my lookup list rather than "#n/a".
Any suggestions on a formula?
I tried =if(vlookup(date,A1:A500,1,0)=1,1,0)
However this still giving me a #n/a answer.

Thanks

It's looking like you're returning a 1 if the date is found in that range.

You could use:
=--(isnumber(match(yourdate,a1:a500,0)))

=match() returns the number of the row in the range that matches (if there's a
match). Otherwise, it returns an error.

=isnumber() returns true or false

and the -- stuff converts those False's to 0's and the True's to 1.



Brian wrote:

I'm using some vlookup formulas to verify dates, and I want to get a "0"
response for dates that are not in my lookup list rather than "#n/a".
Any suggestions on a formula?
I tried =if(vlookup(date,A1:A500,1,0)=1,1,0)
However this still giving me a #n/a answer.

Thanks

--

Dave Peterson