5

Here are the formulas I need but I can not figure out how to combine them.

=IF(H4=1,IF(J30<=E29,"Pre-Qualified","Unqualified"))
=IF(H4=2,IF(J30<=E30,"Pre-Qualified","Unqualified"))
=IF(H4=3,IF(J30<=E31,"Pre-Qualified","Unqualified"))
=IF(H4=4,IF(J30<=E32,"Pre-Qualified","Unqualified"))
=IF(H4=5,IF(J30<=E33,"Pre-Qualified","Unqualified"))
=IF(H4=6,IF(J30<=E34,"Pre-Qualified","Unqualified"))
Improve this question

4 Answers 4

Reset to default
16

Because you are testing for 1, 2, 3, …, there’s a special answer that will work for you:

=CHOOSE(H4, IF(J30<=E29,"Pre-Qualified","Unqualified"),
            IF(J30<=E30,"Pre-Qualified","Unqualified"),
            IF(J30<=E31,"Pre-Qualified","Unqualified"),
            IF(J30<=E32,"Pre-Qualified","Unqualified"),
            IF(J30<=E33,"Pre-Qualified","Unqualified"),
            IF(J30<=E34,"Pre-Qualified","Unqualified"))

This has the same effect as the compound IF-THEN formula given in the other answers.

But, because the six inner formulas are the same except for one element, you can simplify this considerably: 

=IF(J30<=CHOOSE(H4, E29, E30, E31, E32, E33, E34), "Pre-Qualified", "Unqualified")

One difference: the other answers will evaluate to FALSE if H4 is not 1, 2, 3, 4, 5, or 6, whereas my answers will result in a #VALUE! error.

Improve this answer
3
  • 1
    Very nice. This would not allow if the H4 were a letter, or if H4 was a word such a Low, Medium, or High. But it definitely simplifies the problem for the OP! As an amatuer Excel user, I have never been privy to this one. Thanks!
    – Damon
    Commented Jan 12, 2015 at 23:05
  • Well, yes; I said that CHOOSE is special (specific) to the 1, 2, 3, ..., problem. But my second simplification is applicable even for other situations: =IF(J30<=IF(H4="one", E29, IF(H4="two", E30, ...)), "Pre-Qualified", "Unqualified")
    – G-Man Says 'Reinstate Monica'
    Commented Jan 12, 2015 at 23:10
  • As E29:E34 is a contiguous range you could also use INDEX like this: =IF(J30<=INDEX(E29:E34,H4),"Pre-Qualified","Unqualified")...and if H4 were text you could use a MATCH function in place of H4
    – barry houdini
    Commented Jan 13, 2015 at 0:06
4 
=IF(H4=1,IF(J30<=E29,"Pre-Qualified","Unqualified"),
IF(H4=2,IF(J30<=E30,"Pre-Qualified","Unqualified")),
IF(H4=3,IF(J30<=E31,"Pre-Qualified","Unqualified")),
....)

This will teste the 2nd if statement if the first one fails. So if H4 != 1 it will test H4 agains 2 ...

Improve this answer
0
3

IF statements are IF, THEN, ELSE. So put the next "IF" in the ELSE place of the last IF statement.

=IF(H4=1,IF(J30<=E29,"Pre-Qualified","Unqualified"),
 IF(H4=2,IF(J30<=E30,"Pre-Qualified","Unqualified"),
 IF(H4=3,IF(J30<=E31,"Pre-Qualified","Unqualified")...)))
Improve this answer
0

You might try an if-and statement like this:

=IF(AND(H1<4,J30<=E29),"Pre-Qualified","Unqualified")

you can enter multiple variables in your and statement and if ALL of them are true,it will return TRUE, if any are not true, it will return false. Another thing you might try is =IF(OR( it works the same as AND except if any are true, it will return true for the entire statement.

Improve this answer
1
  • Umm... the OP can say =5+12, too, but that won't answer his question, either. How, exactly, do you see this as solving the stated problem?
    – G-Man Says 'Reinstate Monica'
    Commented Jan 16, 2015 at 20:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .