Maximum after applying multiple criteria in a table

[chriskon]

Hello,

I am trying to find a formula doing the following:

I have three columns that I would like look for criteria to be met at each row, and then from a fourth column to enter the maximum value that represent the several findings. For instance, in the given screenshot, the first criterion is for the cell in column G to equal "accounting standards", then the second criterion for the same row in column H the cell to equal "Internal" and the third criterion in column J same row to equal "Threat". If all these criteria are met, I would like to have the maximum value from column L that corresponds to the one or more rows that are found to meet the 3 criteria. Is that possible? The thing is that I would like results in the same row but in case there are more than one row as result, to find the maximum of these 2,3 or more from column L. Is that possible? I am not very familiar with Excel. Thank you in advance for your time!

https://postimg.org/image/u0j22dihp/

Kind regards,

CK.

 

[Special-K99]

If youre gonna post a link make sure its to a spreadsheet not an image otherwise we'll all have to reenter the data just to test their solution.

There's software on this forum for posting images.

 

[chriskon]

If youre gonna post a link make sure its to a spreadsheet not an image otherwise we'll all have to reenter the data just to test their solution.

There's software on this forum for posting images.

Thank you. My apologies that I did not consider of that. How do I upload an excel file here?

 

[Special-K99]

You cant post an Excel file on this forum but you can upload a link as youve done though moderators prefer images (though that won't tell us if extra spaces are in the data which is a common problem).

To post an image on here there is something called HTML Maker but ive never used it

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Sub-category[/TD]
[TD]Internal Or External Drivers[/TD]
[TD][/TD]
[TD]Threat / Opportunity[/TD]
[TD]Risk Threat /Opportunity[/TD]
[TD][/TD]
[TD]max[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]People Skill[/TD]
[TD]Internal[/TD]
[TD]Employ Engament[/TD]
[TD]Threat[/TD]
[TD][/TD]
[TD]

12​

[/TD]
[TD]

25​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]People Skill[/TD]
[TD]Internal[/TD]
[TD]Employ Retention[/TD]
[TD]Threat[/TD]
[TD][/TD]
[TD]

20​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]People Skill[/TD]
[TD]Internal[/TD]
[TD]Employ Development[/TD]
[TD]Threat[/TD]
[TD][/TD]
[TD]

9​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Accounting Standard[/TD]
[TD]Internal[/TD]
[TD]Recruitmet Pratices[/TD]
[TD]Threat[/TD]
[TD][/TD]
[TD]

20​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Regulatory Requirement[/TD]
[TD]External[/TD]
[TD]Legislative Compliance[/TD]
[TD]Threat[/TD]
[TD][/TD]
[TD]

8​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Accounting Standard[/TD]
[TD]Internal[/TD]
[TD]Supporting Business Manager[/TD]
[TD]Threat[/TD]
[TD][/TD]
[TD]

25​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

M2=MAX(IF($G$2:$G$7=G5,IF($H$2:$H$7=H2,IF($J$2:$J$7=$J$2,$L$2:$L$7))))

Control+ Shift+ Enter