IF AND or IF OR - HELP

[callahanska]

I'm sure there is a more efficient way to write this formula. I'm hoping my Excel Brain Hive and help. I've attempted to break it up in lines.

=if
(AND(M3="Low",N3="Low",O3="Entry/Lower Level"),"No Change",(AND(M3="Low",N3="Medium",O3="Entry/Lower Level"),"No Change",(AND(M3="Low",N3="High",O3="Entry/Lower Level"),"Minimum Range",
(AND(M3="Low",N3="Low",O3="Mid-Level Experience"),"No Change",(AND(M3="Low",N3="Medium",O3="Mid-Level Experience"),"No Change",(AND(M3="Low",N3="High",O3="Mid-Level Experience"),"Minimum Range",
(AND(M3="Low",N3="Low",O3="Very Experienced"),"No Change",(AND(M3="Low",N3="Medium",O3="Very Experienced"),"Minimum Range",(AND(M3="Low",N3="High",O3="Very Experienced"),"Medium Range", (AND(M3="Medium",N3="Low",O3="Entry/Lower Level"),"Minimum Range",(AND(M3="Medium",N3="Medium",O3="Entry/Lower Level"),"Minimum Range",(AND(M3="Medium",N3="High",O3="Entry/Lower Level"),"Minimum Range",
(AND(M3="Medium",N3="Low",O3="Mid-Level Experience"),"Medium Range",(AND(M3="Medium",N3="Medium",O3="Mid-Level Experience"),"Medium Range",(AND(M3="Medium",N3="High",O3="Mid-Level Experience"),"Medium Range",
(AND(M3="Medium",N3="Low",O3="Very Experienced"),"Medium Range",(AND(M3="Medium",N3="Medium",O3="Very Experienced"),"Medium Range",(AND(M3="Medium",N3="High",O3="Very Experience"),"High Range",
(AND(M3="High",N3="Low",O3="Entry/Lower Level"),"Minimum Range",(AND(M3="High",N3="Medium",O3="Entry/Lower Level"),"Medium Range",(AND(M3="High",N3="High",O3="Entry/Lower Level"),"Medium Range",
(AND(M3="High",N3="Low",O3="Mid-Level Experience"),"Medium Range",(AND(M3="High",N3="Medium",O3="Mid-Level Experience"),"Medium Range",(AND(M3="High",N3="High",O3="Mid-Level Experience"),"Medium to High Range",
(AND(M3="High",N3="Low",O3="Very Experienced"),"Medium Range",(AND(M3="High",N3="Medium",O3="Very Experienced"),"Medium to High Range",(AND(M3="High",N3="High",O3="Very Experienced"),"High Range",
False)))))))))))))))))))))))))))

Thank you in advance!

 

[6StringJazzer]

I would set up a table with your decision matrix, instead of baking it into a formula. Put the following table on a new sheet. Call the sheet Matrix. I don't know what the Low/Medium/High means in columns M and N so I just made up names.

Then your formula is:

=VLOOKUP(M3&"+"&N3&"+"&O3, Matrix!$D$2:$E$28, 2, FALSE)

Fill out column E of the matrix with your desired results. It can be seen in your statement above but I didn't have time to sort them all out and type them in.


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]

Level​

[/td][td]

Height​

[/td][td]

Experience​

[/td][td]

Key​

[/td][td]

Result​

[/td][/tr]

[tr][td]

2​

[/td][td]

Low​

[/td][td]

Low​

[/td][td]

Entry/Lower Level​

[/td][td]

Low+Low+Entry/Lower Level​

[/td][td]

No Change​

[/td][/tr]

[tr][td]

3​

[/td][td]

Low​

[/td][td]

Low​

[/td][td]

Mid-Level Experience​

[/td][td]

Low+Low+Mid-Level Experience​

[/td][td]

​

[/td][/tr]

[tr][td]

4​

[/td][td]

Low​

[/td][td]

Low​

[/td][td]

Very Experienced​

[/td][td]

Low+Low+Very Experienced​

[/td][td]

​

[/td][/tr]

[tr][td]

5​

[/td][td]

Low​

[/td][td]

Medium​

[/td][td]

Entry/Lower Level​

[/td][td]

Low+Medium+Entry/Lower Level​

[/td][td]

​

[/td][/tr]

[tr][td]

6​

[/td][td]

Low​

[/td][td]

Medium​

[/td][td]

Mid-Level Experience​

[/td][td]

Low+Medium+Mid-Level Experience​

[/td][td]

​

[/td][/tr]

[tr][td]

7​

[/td][td]

Low​

[/td][td]

Medium​

[/td][td]

Very Experienced​

[/td][td]

Low+Medium+Very Experienced​

[/td][td]

​

[/td][/tr]

[tr][td]

8​

[/td][td]

Low​

[/td][td]

High​

[/td][td]

Entry/Lower Level​

[/td][td]

Low+High+Entry/Lower Level​

[/td][td]

​

[/td][/tr]

[tr][td]

9​

[/td][td]

Low​

[/td][td]

High​

[/td][td]

Mid-Level Experience​

[/td][td]

Low+High+Mid-Level Experience​

[/td][td]

​

[/td][/tr]

[tr][td]

10​

[/td][td]

Low​

[/td][td]

High​

[/td][td]

Very Experienced​

[/td][td]

Low+High+Very Experienced​

[/td][td]

​

[/td][/tr]

[tr][td]

11​

[/td][td]

Medium​

[/td][td]

Low​

[/td][td]

Entry/Lower Level​

[/td][td]

Medium+Low+Entry/Lower Level​

[/td][td]

​

[/td][/tr]

[tr][td]

12​

[/td][td]

Medium​

[/td][td]

Low​

[/td][td]

Mid-Level Experience​

[/td][td]

Medium+Low+Mid-Level Experience​

[/td][td]

​

[/td][/tr]

[tr][td]

13​

[/td][td]

Medium​

[/td][td]

Low​

[/td][td]

Very Experienced​

[/td][td]

Medium+Low+Very Experienced​

[/td][td]

​

[/td][/tr]

[tr][td]

14​

[/td][td]

Medium​

[/td][td]

Medium​

[/td][td]

Entry/Lower Level​

[/td][td]

Medium+Medium+Entry/Lower Level​

[/td][td]

​

[/td][/tr]

[tr][td]

15​

[/td][td]

Medium​

[/td][td]

Medium​

[/td][td]

Mid-Level Experience​

[/td][td]

Medium+Medium+Mid-Level Experience​

[/td][td]

​

[/td][/tr]

[tr][td]

16​

[/td][td]

Medium​

[/td][td]

Medium​

[/td][td]

Very Experienced​

[/td][td]

Medium+Medium+Very Experienced​

[/td][td]

​

[/td][/tr]

[tr][td]

17​

[/td][td]

Medium​

[/td][td]

High​

[/td][td]

Entry/Lower Level​

[/td][td]

Medium+High+Entry/Lower Level​

[/td][td]

​

[/td][/tr]

[tr][td]

18​

[/td][td]

Medium​

[/td][td]

High​

[/td][td]

Mid-Level Experience​

[/td][td]

Medium+High+Mid-Level Experience​

[/td][td]

​

[/td][/tr]

[tr][td]

19​

[/td][td]

Medium​

[/td][td]

High​

[/td][td]

Very Experienced​

[/td][td]

Medium+High+Very Experienced​

[/td][td]

​

[/td][/tr]

[tr][td]

20​

[/td][td]

High​

[/td][td]

Low​

[/td][td]

Entry/Lower Level​

[/td][td]

High+Low+Entry/Lower Level​

[/td][td]

​

[/td][/tr]

[tr][td]

21​

[/td][td]

High​

[/td][td]

Low​

[/td][td]

Mid-Level Experience​

[/td][td]

High+Low+Mid-Level Experience​

[/td][td]

​

[/td][/tr]

[tr][td]

22​

[/td][td]

High​

[/td][td]

Low​

[/td][td]

Very Experienced​

[/td][td]

High+Low+Very Experienced​

[/td][td]

​

[/td][/tr]

[tr][td]

23​

[/td][td]

High​

[/td][td]

Medium​

[/td][td]

Entry/Lower Level​

[/td][td]

High+Medium+Entry/Lower Level​

[/td][td]

​

[/td][/tr]

[tr][td]

24​

[/td][td]

High​

[/td][td]

Medium​

[/td][td]

Mid-Level Experience​

[/td][td]

High+Medium+Mid-Level Experience​

[/td][td]

​

[/td][/tr]

[tr][td]

25​

[/td][td]

High​

[/td][td]

Medium​

[/td][td]

Very Experienced​

[/td][td]

High+Medium+Very Experienced​

[/td][td]

​

[/td][/tr]

[tr][td]

26​

[/td][td]

High​

[/td][td]

High​

[/td][td]

Entry/Lower Level​

[/td][td]

High+High+Entry/Lower Level​

[/td][td]

​

[/td][/tr]

[tr][td]

27​

[/td][td]

High​

[/td][td]

High​

[/td][td]

Mid-Level Experience​

[/td][td]

High+High+Mid-Level Experience​

[/td][td]

​

[/td][/tr]

[tr][td]

28​

[/td][td]

High​

[/td][td]

High​

[/td][td]

Very Experienced​

[/td][td]

High+High+Very Experienced​

[/td][td]

​

[/td][/tr]
[/table]