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"))
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.

  • 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 Jan 12 '15 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 Jan 12 '15 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 Jan 13 '15 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 ...

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")...)))
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.

  • 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 Jan 16 '15 at 20:19

protected by Community Apr 8 '17 at 9:53

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.