=if(isna(vlookup(...)),"not available",if(vlookup(...)="","Not available",
vlookup(...)))

or

=if(iserror(1/len(vlookup(...))),"not available",vlookup(...))



Raz wrote:

I guess I am not making sense here, or you are not getting my problem.

So, i will explain the situation a little more.

here is how my workseet looks.

A B L M
1 John 400 John 400
2 Mack 500

I have the equation/function in B1, when i type John in A1 the function
should lookup in the columns L and M for 'John' and corresponding number in
Column M.
Then the function should return 400 (this case). But if the M1 is blank,
then the function should return "Not Available" in B2.

I hope this make sense, all the previous equations were returning 0, where I
want it to return "Not available" if M1 is blank.

--

Dave Peterson