# If statement/and statement with 4 possible results with numeric range

#### ramosca

##### New Member
Hi, I am working on a report and need some assistance. I am working with Windows XP Professional and Excel 2007.

My report is looking for a formula that will reflect if the goal is in jeopardy of being missed.

If cell A2 = "COMP" the result should be "NO"
If cell A2 = "PENDING" and cell B2 is any number between 0 thru 9 the result should be "PENDING"
If cell A2 = "PENDING" and cell B2 is any number between 10 thru 15 the result should be "YES"
If cell A2 = "PENDING" and cell B2 is greater or equal to 16 the result should be "FAIL"

Any help would be GREATLY appreciated!!

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Here's an approach:

Note that you did not specify if Col A equals anything else (i.e. other than COMP or PENDING) so this approach does not test for that. It assumes that Col A will only be COMP or PENDING and so the formula only tests for COMP, and if it isn't COMP then it assumes it to be PENDING by default.

Sheet5
A
B
C
1
2
COMP
5
NO
3
PENDING
PENDING
4
PENDING
11
YES
5
PENDING
15
YES
6
PENDING
16
FAIL
7
PENDING
25
FAIL
8
9
10
----------------
----------
----------------
11

<tbody>
</tbody>
Excel 2010

Worksheet Formulas
Cell
Formula
C2
=IF(A2="COMP","NO",LOOKUP(B2,{0,10,16},{"PENDING","YES","FAIL"}))
C3
=IF(A3="COMP","NO",LOOKUP(B3,{0,10,16},{"PENDING","YES","FAIL"}))
C4
=IF(A4="COMP","NO",LOOKUP(B4,{0,10,16},{"PENDING","YES","FAIL"}))
C5
=IF(A5="COMP","NO",LOOKUP(B5,{0,10,16},{"PENDING","YES","FAIL"}))
C6
=IF(A6="COMP","NO",LOOKUP(B6,{0,10,16},{"PENDING","YES","FAIL"}))
C7
=IF(A7="COMP","NO",LOOKUP(B7,{0,10,16},{"PENDING","YES","FAIL"}))

<tbody>
</tbody>

<tbody>
</tbody>

Maybe

=IF(A2="COMP","No",IF(A2="PENDING",LOOKUP(B2,{0;10;16},{"PENDING";"YES";"FAIL"}),""))

M.

Thanks guys so much, both work amazingly!!

Happy to help. Glad that it worked out for you and thank you for the feedback.

Replies
3
Views
184
Replies
9
Views
253
Replies
3
Views
222
Replies
25
Views
450
Replies
9
Views
707

1,203,470
Messages
6,055,604
Members
444,803
Latest member
retrorocket129

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back