Forum Discussion
Formula based on a combination of two other cells where the combination varies
Hello forum friends
I need a formula that will look at two other cells and depending on the combination in those cells populate the third cell. For example:
If A1 says Critical and B1 says High then C1 will = Priority 1.
For example, the combinations will be as follows:
| A1 | B1 | C1 (cell with the formula) |
| Critical | High | Priority 1 |
| Critical | Medium | Priority 1 |
| Critical | Low | Priority 2 |
| Urgent | High | Priority 1 |
| Urgent | Medium | Priority 2 |
| Urgent | Low | Priority 3 |
| Routine | High | Priority 2 |
| Routine | Medium | Priority 3 |
| Routine | Low | Priority 4 |
| Complex | High | Priority 2 |
| Complex | Medium | Priority 4 |
| Complex | Low | Priority 4 |
| Ongoing | Doesn't matter what is in this column, Answer will always be Ongoing | Ongoing |
Thanks in advance for your help!
jmdarbyshire That could be:
="Priority " & LOOKUP(B1,{"High","Low","Medium"},{1,3,2})--(A1="Routine")Riny_van_Eekelen Thank you for your help! Would it be possible to explain how that function works? Just in case I need to adjust the formula as I develop the prioritisation.
For future modification I'd add two helper tables which could at any place of the workbook
Any lookup formulae could be to generate Priority, e.g.
="Priority " & SUMPRODUCT( (Importance[Importance] = TRIM($H5) ) * Importance[Level] + (Sensitivity[Sensitivity] = TRIM($G5) ) * Sensitivity[Level] )for the current logic.
Riny_van_Eekelen Well, I gathered the High, Medium, Low would always return a Priority 1, 2 and 3, except when the 'Level' in A would be "Routine". Then you would add 1.
So, what this formula does is lookup the word in B in the array within the the first pair of curly brackets (which must be in alphabetical order) and then return the values from the second array between curly brackets. Finally, it adds 1 in case the value in A equals "Routine". The -- turns a TRUE/FALSE value into 1 or 0. Try it out for yourself in steps by breaking down the formula in smaller segments.
- Hi Riny
Thank you again for your help. I have updated my initial post with a few more entries, which may change your suggested formula. Can you suggest a formula for this now?
Thank you so much for your help.
A 365 approach that blends into my spreadsheets but probably not anyone else's would be
= Priorityλ(Urgency, Importance)where the function Priorityλ is defined by
Priorityλ = LAMBDA(urgency, importance, LET( priorityArr, "Priority" & {1, 2, 3; 1, 2, 3; 2, 3, 4}, urgencyIndex, XMATCH(urgency, {"Critical", "Urgent", "Routine"}), importanceIndex, XMATCH(importance, {"High", "Medium", "Low"}), priority, INDEX(priorityArr, urgencyIndex, importanceIndex), priority ) )- Hello, thank you Peter. This one looks complicated. How do I input this in Excel?
I understand your problem. The solution is not really difficult; it is just that it is built upon a very different development strategy that is only available in Excel 365 and is unfamiliar to most users.
The function 'Priorityλ' is Defined Name and the formula that follows it is what it 'Refers to'. That can be set up in Name Manager or within the 'Advanced Formula Editor' which is an improved version of both Name Manager and the formula editing bar.
Once the function is defined as a Name, it is evaluated from a worksheet cell by placing it within a formula following the '=' sign.
There is much to learn but there are also complexities that can be forgotten. I do not have to concern myself with 'what does $DA375 contain' because I use only absolute references and I name all ranges that contain business information so that I need not hunt around the workbook to determine the significance of a reference. I also tend to have very few formulas on a worksheet, so there is much less to audit.
Hi jmdarbyshire
You can use the below formula to perform your task.
=IFERROR(INDEX($G$1:$J$6,MATCH(A1,$G$1:$G$6,0),MATCH(B1,$G$1:$J$1,0)),"")
I am attaching the excel file with the same formula.
Respected Sir, SergeiBaklan
I got your point.
It is mentioned in the original post but I think I missed that point.
Here formula is little bit tweaked as below
=IFERROR(IF(A17=$G$6,A17,INDEX($G$1:$J$6,MATCH(A17,$G$1:$G$6,0),MATCH(B17,$G$1:$J$1,0))),"Not Available")
I am attaching the excel file with new formula in D column.
