VBA/Formula - Max values including Indexing, offset, match etc.

[Koefoed]

Hello all,

I'm quite new to excel, and i need some help figuring out a specific formula, and if not possible a VBA code instead.

The following picture shows the visual setup:


Color indications
Orange - Variables/conditions:
Orange is the variables. i want the Formula/VBA to match B23:B25 with 1:1

Blue - Indexing:
Blue is the indexing. When B23:B25 have been matched with 1:1, i want the Formula/VBA to do as follows:

• Primary variable (B23) - Find the maximum total value (The combination of one type a, one type b, one type c, one type d, and one type e), in the column matched (above case Column C).
• Secondary & Tertiary (B24) - I call these extra criteria, and the maximization of these are of secondary priority compared to the primary variable. Also as shown in C24:C25, these variables can be set to a minimum total value. As shown in the visual above, color brown, and purple illustrates total sum of the secondary variables.

Red - Wrong selection:
Red indicates the wrong selection. As you can see, in column Z; type c, there are two rows (KKK and III), with the same maximum value => 12%. If name "KKK" is selected, Secondary criteria of minimum 25% won't be fulfilled, however if "III" is selected it will.

Green - return/Pasting:
Green is the value i want to be returned to B27:B31, the ones that matches the best solution.

Progress so far
Color gray, indicates the formula i've been working on so far. It is able to locate the maximum value for the primary variable, and return the name into cell F31. What it can't do is fint the one solution, where the maximum value of all five types are calculated, and take the secondary and tertiary criteria into consideration.
Formula: INDEX(A:A;MATCH(MAX(OFFSET(A:A;;MATCH(D27;1:1;0)-1));OFFSET(A:A;;MATCH(D27;1:1;0)-1);0))

I hope this makes sense, and that someone will be able to help me. Also if you just have ideas to peaces of the solution, it would be greatly appreciated.

Best regards
Koefoed

 

[Koefoed]

I got a bit further by myself by following formula:
INDEX(A:A;MATCH(MAX(IF(B:B="a";(OFFSET(A:A;;MATCH(E31;1:1;0)-1));));OFFSET(A:A;;MATCH(E31;1:1;0)-1);0))&
INDEX(A:A;MATCH(MAX(IF(B:B="b";(OFFSET(A:A;;MATCH(E31;1:1;0)-1));));OFFSET(A:A;;MATCH(E31;1:1;0)-1);0))&
INDEX(A:A;MATCH(MAX(IF(B:B="C";(OFFSET(A:A;;MATCH(E31;1:1;0)-1));));OFFSET(A:A;;MATCH(E31;1:1;0)-1);0))&
INDEX(A:A;MATCH(MAX(IF(B:B="d";(OFFSET(A:A;;MATCH(E31;1:1;0)-1));));OFFSET(A:A;;MATCH(E31;1:1;0)-1);0))&
INDEX(A:A;MATCH(MAX(IF(B:B="e";(OFFSET(A:A;;MATCH(E31;1:1;0)-1));));OFFSET(A:A;;MATCH(E31;1:1;0)-1);0))

This formula results in F31 in the following Image. However, i want to add the following to the formula:
The sum of secondary criteria V (B24) referring to column F, of the five selected max values - row 3,8,10,15,21, must be above 25% (C24).
The formula should then change the result in F31 from BBBGGGIIINNNTTT to BBBGGGKKKNNNTTT.

Hope this makes sense.

 

[Koefoed]

I'll give it a last try