# Formula based on a combination of two other cells where the combination varies

Copper Contributor

# 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

20 Replies

# Re: Formula based on a combination of two other cells where the combination varies

@jmdarbyshire That could be:

``="Priority " & LOOKUP(B1,{"High","Low","Medium"},{1,3,2})--(A1="Routine")``

# Re: Formula based on a combination of two other cells where the combination varies

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

# Re: Formula based on a combination of two other cells where the combination varies

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
)
)``````

# Re: Formula based on a combination of two other cells where the combination varies

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

# Re: Formula based on a combination of two other cells where the combination varies

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.

# Re: Formula based on a combination of two other cells where the combination varies

Hello, thank you Peter. This one looks complicated. How do I input this in Excel?

# Re: Formula based on a combination of two other cells where the combination varies

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.

# Re: Formula based on a combination of two other cells where the combination varies

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!

# Re: Formula based on a combination of two other cells where the combination varies

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.

# Re: Formula based on a combination of two other cells where the combination varies

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

# Re: Formula based on a combination of two other cells where the combination varies

@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?

# Re: Formula based on a combination of two other cells where the combination varies

Formula uses names defined in Name Manager

In your own file you need to set the accordingly. Or use references instead.

# Re: Formula based on a combination of two other cells where the combination varies

Thank you! This is excellent!

# Re: Formula based on a combination of two other cells where the combination varies

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

# Re: Formula based on a combination of two other cells where the combination varies

It shall return Ongoing into the right cell with any text in the middle cell, not as

# Re: Formula based on a combination of two other cells where the combination varies

Respected Sir, @SergeiBaklan

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.

# Re: Formula based on a combination of two other cells where the combination varies

How would I tweak the formula so that if there is no entry in the urgency or importance column, nothing shows up in the priority column?

# Re: Formula based on a combination of two other cells where the combination varies

As variant

``````=IF(  TRIM(\$C4) = "Ongoing", "Ongoing",
IF( (TRIM(\$C4) = "")*(TRIM(\$D4) = ""), "",
"Priority " &
XLOOKUP( TRIM(\$D4), Importance,
XLOOKUP( TRIM(\$C4), Sensivity, Priority, "not defined" ),
"not defined")
))``````

with

# Re: Formula based on a combination of two other cells where the combination varies

Thank you very much for all your help Sergei. It works perfectly.