Multiple Logical Tests in IF function

[Guest]

dear members,

I want to perform multiple logical test in the IF function, how can i do that.
IF function takes only single logical test. for example :

IF DEPT1=COMPUTERS THEN FIRST ELSE NONE,
IF DEPT1=ENGLISH THEN SECOND ELSE NONE,
IF DEPT1=SCIENCE THEN THIRD ELSE NONE ....... and so on......

as u can see above the IF function should take multiple arguments and
according to the condition should display the result. IF function in excel
only takes one argument .

could any one help me out in this. Is there any function in excel which
takes multiple logical tests.

thanks
regards
sandeep

 

[Guest]

Since you are posting in the programming section I assume you are writing
code. That being the case you probably want a Select Case statement...
something like this...

Select Case DEPT1
Case "COMPUTERS"
msgbox "COMPUTERS"
Case "ENGLISH"
msgbox "ENGLISH"
Case "SCIENCE"
msgbox "SCIENCE"
Case Else
msgbox "How did I get here???"
end Select

 

[Bob Phillips]

=VLOOKUP(DEPT1,{"Computers","First";"English","Second";"Science","Third"},2,
False)

extend as required

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Conan Kelly]

sandeep,

if you are trying to come up with a formula for a cell, use nested IF functions like this:

=IF(A1="Computers","First",IF(A1="English","Second",IF(A1="Science","Third","")))

now I don't know if there is a limit on how many nested IF functions you can have. Someone else might know.




If you need code for VBA, you can do it like this:

If DEPT1 = "Computers" Then
"First" '<--- Probably want to set a string variable to "First"
Else If DEPT1 = "English" Then
"Second"
Else If DEPT1 = "Science" Then
"Third"
Else
""
End If

That will to the same thing as the SELECT CASE statement that Jim Thomlinson posted.

I hope this helps,

Conan Kelly

 

[Guest]

The limit is 7. You can get around it but it is a bit of work... Check out
this link...

http://www.cpearson.com/excel/nested.htm