what is wrong with this function?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to place an "F" in E2 if D2 equals any of the following, else "N

=IF(D2="9128",IF(D2="3133",IF(D2="3135",IF(D2="3128",IF(D2="3129",IF(D2="3134","F","N")))))

I also tried vlooku

=vlookup(d2,code,2,false

but getting a #NAME? error
 
Ryan

Try

IF(OR(D2=9128,D2=3133,D2=3135,D2=3128,D2=9129,D2=3134)
,"F","N")

Charlie O'Neill
 
The Subject was "What is wrong with this function"

The answers that you got will work, but as I see it, what is *wrong* with
what you wrote is as follows

As you know, the syntax for an IF statement is:
IF(Test, Do it Test is TRUE, Do if Test is FALSE)

Now suppose in D2 you had "3133". The first test, D2="9128", would fail so
XL would not try the "Do if Test TRUE" part of the IF statement. But XL
sees the *TRUE* part of the formula as being the *whole* of:
IF(D2="3133",IF(D2="3135",IF(D2="3128",IF(D2="3129",IF(D2="3134","F","N"))))
)
The formula therefore goes to the *FALSE* part of the first test which is
between the last two brackets and, as there is nothing there, returns FALSE.

To try to explain it a bit simpler consider the formula:

=IF(A1=1,IF(A1=2,IF(A1=3,"Three","Not Three"),"Not Two"),"Not One")

If we enter 1 in A1, the 1st test will be true and so XL will continue on to
the *TRUE* part of the statement which is:
IF(A1=2,IF(A1=3,"Three","Not Three"),"Not Two")

This time the Test **IF(A1=2** fails so XL misses out the *TRUE* part of
the statement:
IF(A1=3,"Three","Not Three")
and goes to the *FALSE* part which is "Not Two"

If we enter 2 in A1 then the 1st test will fail and so XL will miss out what
it sees as the *TRUE* part which is:
IF(A1=2,IF(A1=3,"Three","Not Three"),"Not Two")

and go to the *FALSE* part which is "Not One"

An entry of 3, (or any other entry including text or even a blank cell),
will also fail the 1st test and so the formula will never be able to return
anything else but "Not One" or "Not Two"

Sorry if this is misleading but I hope that it helps you to see what was
wrong

Regards

Sandy
 
Assuming that D2 is tested to house numbers...

=CHAR(78-ISNUMBER(MATCH(D2,{9128,3133,3135,3128,3129,3134},0))*8)
 
Back
Top