When the A1 value is not found within column B, Excel returns N/A instead of 0. This doesn't make much sense to me as the formula is defined as return 1 if there is a match, otherwise return 0. What's the purpose of returning N/A in cases of inequality?
#N/A is a result of the VLOOKUP function.
This gets passed to the IF function and is the final result of the IF function.
Very few functions continue to calculate once an error is generated and the IF function is not one of them.
Consider errors to be "fatal errors" once they're generated unless you specifically write the formula to account for them.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Change to
=IF(ISNA(VLOOKUP($A1,B:B,1,FALSE)=$A1),0,1)
Why is it an error though? The fact that A1 isn't present within B is the exact opposite of presence (i.e. if-then resulting in 1), thus it should be evaluated as if-otherwise (0). An error means a deviation from an expectation. This is however in accordance with my expectations - if it's not found for whatever reason in B, assign "0" to the field.Originally Posted by Tony Valko
I know how to fix this. I opened this thread to find out why N/A =/= A1 isn't assessed as if-otherwise.
Last edited by regresss; 05-30-2015 at 05:51 PM.
One of these...
=COUNT(MATCH(A1,B:B,0))
=--(COUNTIF(B:B,A1)>0)
That's just the way the MS programmer designed the function.
You'll discover a lot of "why does this do this" kind of outcomes.
Do you agree with the way it is programmed? It can be imho useful to work with N/A values, but this is clearly not the case as "0" is never assigned in the practice. It's always either 1 or N/A. Why does it make me to define if-otherwise output then?That's just the way the MS programmer designed the function.
You'll discover a lot of "why does this do this" kind of outcomes.
I can see the benefit of a 0 return as it doesn't "break" the formula or any downstream formulas but I've been using Excel for a lot of years so I'm used to dealing with the errors.
LOOKUP(A1,B:B,1,0)=A1
With it not in Column B - Wouldn't it return a Err.
Then it tries to compare Err = A1 and freaks out.
Last edited by briguin; 05-30-2015 at 06:22 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks