14,642,663 members
Rate this:
See more:
I need to make an IF statement with four outcomes.
The selected cell will be filled in with a number and then looked up against a long range of numbers in another sheet called "Risk".
The outcomes should be "Low Risk", "Medium Risk", "High Risk" and "No go".

A3:A2410 = Low Risk
A2413:A2671 = Medium Risk
A2675:A2954 = High Risk
A2956:A2975 = No go

What I have tried:

I have tried making the IF statement myself, but without luck..
Here is my statement:
=IF(E3=Risk!A3:A2410,"Low Risk",IF(E3=Risk!A2413:2671,"Medium Risk", IF(E3=Risk!A2675:2954, "High risk", "No go")))
Posted
Updated 26-Oct-18 1:05am
v2

Rate this:

## Solution 1

Two problems here:
1) You need to specify sheet names by prefixing them with "\$":
`...=\$Risk!...`

2) You want to look at the COUNTIF function to check if a value is in a range: Excel formula: Value exists in a range | Exceljet[^]
Comments
Member 14033719 26-Oct-18 7:07am

Thanks OriginalGriff,
Can you try typing a new statement for me?
OriginalGriff 26-Oct-18 7:13am

Nope, because I can't test it against your data.
It's pretty simple though - try it on the simplest version first (check one range and use two messages). Then when that works, expand it to two ranges, and so on. It's not difficult!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 185 Maciej Los 165 Richard Deeming 160 Richard MacCutchan 138 Patrice T 100
 OriginalGriff 5,909 Sandeep Mewara 3,131 Richard MacCutchan 2,052 Richard Deeming 1,794 CPallini 1,737

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100