Click here to Skip to main content
14,642,663 members
Rate this:
Please Sign up or sign in to vote.
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")))
Updated 26-Oct-18 1:05am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Two problems here:
1) You need to specify sheet names by prefixing them with "$":

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[^]
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)

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