Formula to assign category based on multiple criteria and then picking highest value

[redsheep79]

Please see attached example.

I'm trying to assign a category to each person based on which categories they have costs in. where clients have costs against multiple categories the main category they will be assigned will be determined as:

If someone has entry in Residential or Home this will be the category assigned to them

If someone has entries in any of the the other columns the category assigned to them will be the one that has the highest value

I would like to have a formula in Column H to automatically do this, if possible. If needed, I could filter out and Residential or Home and only apply formula to the remaining to reduce a layer of any formula.

Thank you.

 

[RobP]

Hi,

could this work for you ?

=IFS(C4<>"","Residential",D4<>"","Home",OR(E4<>"",F4<>"",G4<>""),XLOOKUP(MAX(E4:G4),E4:G4,E$3:G$3))

cheers
Rob

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula to pick highest value form multiple columns
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.