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 Hi...
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.
