MAX of mapped criteria unless another value is lower

Question

I asked this question in the past. What I need to do now is List Dan's Bakery in the Entity final column. The original entities go through a mapping table and the MAX limit is in the Limit final column. Here is that formula, {=MAX(IF(ISNUMBER(MATCH($G$2:$G$110,IF('Mapping Table'!$B$4:$B$204=B25,'Mapping Table'!$A$4:$A$204),0)),$D$2:$D$110))}. In the above formula, the lookup value for Match is the Parent Connection (G) column and the mapping table maps any entities with certain names to a parent and the D column would be the Limit 1 as there is a hidden column. Hope that's clear enough.

I want to now write a similar formula so that whichever Entity has the highest limit and WORST risk rating is shown in the Entity final column. Meaning, in the below example, Dan's Groceries would be the entity I want to produce in the Entity final column because it has a credit limit line of 30 and a better rating (the lower the rating, the better). Any ideas?

EDIT (March 21) : I need a formula that will take into account the mapping it will register the above formula as well as take into the account the ratings.

I changed the formula to, =INDEX($A$1:$A$7,MATCH(MIN((1-IF($B$1:$B$7=B1,$D$1:$D$7)/MIN(IF($B$1:$B$7=B1,$D$1:$D$7)))+IF($B$1:$B$7=B1,$C$1:$C$7)/MAX(IF($B$1:$B$7=B1,$C$1:$C$7))),(1-$D$1:$D$7/MIN(IF($B$1:$B$7=B1,$D$1:$D$7)))+IF($B$1:$B$7=B1,$C$1:$C$7)/MAX(IF($B$1:$B$7=B1,$C$1:$C$7)),0))

but, I am coming across errors. For example, if I changed the Limit column for Other to 30, Dan's Bakery is the result or when I change Dan's Shakes limit to 40 (assuming it will be the correctFinal Entityit seems to be taking the MIN rating as the ultimate decider withDan's Fuel` still as the result.

Answer 1

So if your Limit and Rating worth the same you can scale them to values between 0 and 1. After that simple addition would rate one of Entities as the "winner".

Here is example data and an array formula that can do what you want(Array Formulas are applied with Ctrl + Shift + Enter:

Data:

A               B   C
Dan's Shakes    25  3
Dan's Groceries 30  2   
Dan's Floral    10  2   
Dan's Bakery    30  1   
Dan's Fuel      20  4   

Formula:

=INDEX(A1:A5,MATCH(MAX((1-C1:C5/MAX(C1:C5))+B1:B5/MAX(B1:B5)),(1-C1:C5/MAX(C1:C5))+B1:B5/MAX(B1:B5),0))

Outcome:

A               B   C   D
Dan's Shakes    25  3   Dan's Bakery
Dan's Groceries 30  2   
Dan's Floral    10  2   
Dan's Bakery    30  1   
Dan's Fuel      20  4   

EDIT:

And when you introduce a parent your data would look like this:

A               B           C   D    
Dan's Shakes    Dan Corp    25  3
Dan's Groceries Dan Corp    30  2
Dan's Floral    Dan Corp    10  2
Dan's Bakery    Dan Corp    30  1
Dan's Fuel      Dan Corp    20  4
Something Else  Bob Corp    30  2
Other           Bob Corp    50  1

Your array formula:

=INDEX($A$1:$A$7,MATCH(MAX((1-IF($B$1:$B$7=B1,$D$1:$D$7)/MAX(IF($B$1:$B$7=B1,$D$1:$D$7)))+IF($B$1:$B$7=B1,$C$1:$C$7)/MAX(IF($B$1:$B$7=B1,$C$1:$C$7))),(1-$D$1:$D$7/MAX(IF($B$1:$B$7=B1,$D$1:$D$7)))+IF($B$1:$B$7=B1,$C$1:$C$7)/MAX(IF($B$1:$B$7=B1,$C$1:$C$7)),0))

Result:

A               B           C   D   E
Dan's Shakes    Dan Corp    25  3   Dan's Bakery
Dan's Groceries Dan Corp    30  2   Dan's Bakery
Dan's Floral    Dan Corp    10  2   Dan's Bakery
Dan's Bakery    Dan Corp    30  1   Dan's Bakery
Dan's Fuel      Dan Corp    20  4   Dan's Bakery
Something Else  Bob Corp    30  2   Other
Other           Bob Corp    50  1   Other