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...
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.
- Hi Sergei. Thank you for your suggestion. I like the idea of using a lookup to another table, but I am not sure I understand the formula? I have updated my initial post with some more data, which might change your suggestion?
Thank you so much for your help!That could be like
with formula
=LET( getPriority, XLOOKUP( TRIM($D16), Importance, XLOOKUP( TRIM($C16), Sensivity, Priority, "N/A" ), -1), IF( getPriority = -1, TRIM($C16), IF( getPriority = "N/A", "not defined", "Priority " & getPriority )) )assuming you are on 365 or 2021
SergeiBaklan Thank you for the detailed formula. Unfortunately, I only get #NAME? error. I have created the table where I need to Priority to populate and then the reference table, but I am not sure how the reference table is being referenced?
Urgency Importance Priority High Medium Low Critical High #NAME? Critical 1 1 2 Critical Medium #NAME? Urgent 1 2 3 Critical Low #NAME? Routine 2 3 4 Urgent High #NAME? Complex 2 4 4 Urgent Medium #NAME? Ongoing -1 -1 -1 Urgent Low #NAME? Routine High #NAME? Routine Medium #NAME? Routine Low #NAME?