# Priority Column based on several factors

0

I have several columns and i need the priority column to state wether the priority is high, Medium High, Medium, Low based on criteria in several of the other columns.

High

New members

Members with score 5 or higher

Or members that have score less than 5 but are high risk and have a date blank or not in 2019

Medium High

Score 2-4

No 2019 date

Medium

Score 0-1

No 2019 date

Low

score 0

2019 date

Here are the columns with some data

Date Score Risk Status Priority 10/13/2018 9 Y Retained   2/1/2019 4 N NEW     3 Y Retained   1/14/2017 6 N Retained   1/5/2016 12 Y Retained     1 N Retained   8/25/2018 0 N NEW

### Discussion

This isn't difficult but it needs a workbook with some data. You can attach a workbook by editing your question.
Variatus (rep: 2968) Feb 22, '19 at 6:32 am
Ok i added a small sample if you could help me that would be amazing
cramos1 Mar 1, '19 at 9:30 pm

0

Please try this formula in E2 of your example and copy down.

``=IF(YEAR(\$A2)=2019,"Low",IF(OR(\$C2="y",\$D2="new"),"High",INDEX({"Low","Medium","Medium High","High"},MATCH(\$B2,{0,1,2,5},1))))``

The following logic is applied.

1. If the date's year = 2019 the priority is "Low"
2. If the (Status = New) or (Risk = Y), the priority is "High"
3. If neither of the above applies, priority is decided by Score:
=>5 = "High"
=>2 = "Medium High"
=>1 = "Medium"
=>0   ="Low"
This schedule is laid out in the MATCH part of the formula, i.e.MATCH(\$B2,{0,1,2,5},1)) and may be modified there. It isn't possible to assign 0 score to both "Medium" and "Low".

Here is a variation on the above which arrives at the same result in a different way.

``=INDEX({"Low","Medium","Medium High","High"},MATCH(IF(YEAR(\$A2)=2019,0,IF(OR(\$C2="y",\$D2="new"),100,\$B2)),{0,1,2,5},1))``

In this variation the priority is purely decided by the score but the score itself is modified.

1. If the date's year = 2019 the score = 0
2. If the (Status = New) or (Risk = Y), the score = 100
3. If neither of the above applies, Score is taken from column B