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