I have two columns, Software_ID and Risk_Level. I want to see how can I fetch the maximum risk level per software_id in a new column. The data sample is as follow:

Software_ID, Risk_Level, Max_Risk_Level

A3B0, High, High

A3B0, Medium, High

A3B0, Low, High

B9S1, Medium, Medium

B9S1, Low, Medium

C2J7, Low, Low

D5K8, High, High

D5K8, Low, High

I have tried Maxifs, simple max with nested if, but it doesn't work. Can you please help? Let me know if you need more information. Thanks! Quote Reply

CodePudding user response：

Here's a solution assuming you've converted the text risk levels into numbers:

=MAXIFS(B2:B9,A2:A9,A2:A9)

Your max range is the risk level, criteria range is the what software you want to include in your max, and the criteria would also be software, but this time by line, which the MAXIF function reads properly and creates a dynamic array.

Without dynamic array: =MAXIFS(B2:B9,A2:A9,A2)