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