Adding criterea to find matching sequence

I have a meal planning spreadsheet that calculates daily meal macros. I have a food list with 4 macros and I would like to find the food that best matches the macros from a target sequence.

I am able to get the closest matching sequence from this thread: [https://stackoverflow.com/questions/66490076/how-do-i-search-for-most-similar-sequence-in-excel], but two additional criterea might be needed.

I was able to find matching foods, macro by macro, with this formula, but it doesn't match sequence by sequence: =INDEX($A$2:$A$200;EQUIV(MIN(ABS($C$2:$C$200-$K$4));ABS($C$2:$C$200-$K$4);0))

I was able to calculate the spread of the sequence with this formula: =SUM(IF($I$3:$L$3<>"";ABS($I$3:$L$3-$D2:$G2);0))

I was able to find the match to the closest sequence with this formula: =VLOOKUP(MIN(A2:A144);A2:B144;2;0) example spreadsheet

What I can't find how to do is:

1-prioritize macros with largest spread without going over the total calories (or at least by not more than 5) 2-less important, search by matching food category

I do have access to the LET function if needed My formulas are translate from French, so hopefully I got the function names right

CALCUL	Name	Cat	Calories	Protein	Carbs	Fat	Calories	Protein	Carbs	Fat
46	Orange	snack	90	2	22	0	86,58	15,15	40,35	-11,31
50	Chobani Non-fat Plai	snack	90	16	6	0
50	Pear	snack	96	0,63	25,66	0,2	Category	snack
54	Ezekiel bread	ingr	80	4	15	0	Suggestion	=VLOOKUP(MIN(A2:A144),A2:B144,2,0)
57	Turkey Deli Slices	ingr	81	14,4	2,7	1,7
58	Brussel Sprout cook	veg	88	3,95	11,06	4,43
59	Pangasius poached	ingr	90	17	0	2

Solution

Paste this in a new sheet:

CALCUL	Name	Cat	Calories	Proteins	Carbs	Fats	Category	Calories	Proteins	Carbs	Fats		Index	Name	Cat	Calories	Proteins	Carbs	Fat	Cat	Calories	Delta proteins multiplied	Delta carbs multiplied	Delta fats multiplied	Total difference	Min difference	Match		Name	Cat	Calories	Proteins	Carbs	Fat
46	Orange	snack	90	2	22	0	ingr	90	10	5	0	=ROWS($P$2:P2)&"° suggestion"	`=AGGREGATE(15,6,ROW($A:$A)/((ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0)=AGGREGATE(15,6,(ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0),ROWS($Q$2:Q2))),1)`	=INDEX(B:B,$Q2)	=INDEX(C:C,$Q2)	=INDEX(D:D,$Q2)	=INDEX(E:E,$Q2)	=INDEX(F:F,$Q2)	=INDEX(G:G,$Q2)	=IF(OR($I$2="",C2=$I$2),IF(C2="","",C2),"")	=IF(AND(D2<=$J$2,D2<>0),D2,"")	=ABS(E2-$K$2)*$K$5	=ABS(F2-$L$2)*$L$5	=ABS(G2-$M$2)*$M$5	=IF(OR(Z2="",AA2=""),"",SUM(AB2:AD2))	=AGGREGATE(15,6,AE:AE,ROWS($AG$2:AG2))	=AGGREGATE(15,6,ROW(AE:AE)/(AE:AE=AG2),COUNTIF($AG$2:AG2,AG2))	=ROWS($AJ$2:AJ2)&"° suggestion"	=INDEX(B:B,$AH2)	=INDEX(C:C,$AH2)	=INDEX(D:D,$AH2)	=INDEX(E:E,$AH2)	=INDEX(F:F,$AH2)	=INDEX(G:G,$AH2)
50	Chobani Non-fat Plai	snack	90	16	6	0						=ROWS($P$2:P3)&"° suggestion"	`=AGGREGATE(15,6,ROW($A:$A)/((ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0)=AGGREGATE(15,6,(ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0),ROWS($Q$2:Q3))),1)`	=INDEX(B:B,$Q3)	=INDEX(C:C,$Q3)	=INDEX(D:D,$Q3)	=INDEX(E:E,$Q3)	=INDEX(F:F,$Q3)	=INDEX(G:G,$Q3)	=IF(OR($I$2="",C3=$I$2),IF(C3="","",C3),"")	=IF(AND(D3<=$J$2,D3<>0),D3,"")	=ABS(E3-$K$2)*$K$5	=ABS(F3-$L$2)*$L$5	=ABS(G3-$M$2)*$M$5	=IF(OR(Z3="",AA3=""),"",SUM(AB3:AD3))	=AGGREGATE(15,6,AE:AE,ROWS($AG$2:AG3))	=AGGREGATE(15,6,ROW(AE:AE)/(AE:AE=AG3),COUNTIF($AG$2:AG3,AG3))	=ROWS($AJ$2:AJ3)&"° suggestion"	=INDEX(B:B,$AH3)	=INDEX(C:C,$AH3)	=INDEX(D:D,$AH3)	=INDEX(E:E,$AH3)	=INDEX(F:F,$AH3)	=INDEX(G:G,$AH3)
50	Pear	snack	96	0,63	25,66	0,2			Proteins	Carbs	Fat	=ROWS($P$2:P4)&"° suggestion"	`=AGGREGATE(15,6,ROW($A:$A)/((ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0)=AGGREGATE(15,6,(ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0),ROWS($Q$2:Q4))),1)`	=INDEX(B:B,$Q4)	=INDEX(C:C,$Q4)	=INDEX(D:D,$Q4)	=INDEX(E:E,$Q4)	=INDEX(F:F,$Q4)	=INDEX(G:G,$Q4)	=IF(OR($I$2="",C4=$I$2),IF(C4="","",C4),"")	=IF(AND(D4<=$J$2,D4<>0),D4,"")	=ABS(E4-$K$2)*$K$5	=ABS(F4-$L$2)*$L$5	=ABS(G4-$M$2)*$M$5	=IF(OR(Z4="",AA4=""),"",SUM(AB4:AD4))	=AGGREGATE(15,6,AE:AE,ROWS($AG$2:AG4))	=AGGREGATE(15,6,ROW(AE:AE)/(AE:AE=AG4),COUNTIF($AG$2:AG4,AG4))	=ROWS($AJ$2:AJ4)&"° suggestion"	=INDEX(B:B,$AH4)	=INDEX(C:C,$AH4)	=INDEX(D:D,$AH4)	=INDEX(E:E,$AH4)	=INDEX(F:F,$AH4)	=INDEX(G:G,$AH4)
54	Ezekiel bread	ingr	80	17	0	2		Weights	1	1	1									=IF(OR($I$2="",C5=$I$2),IF(C5="","",C5),"")	=IF(AND(D5<=$J$2,D5<>0),D5,"")	=ABS(E5-$K$2)*$K$5	=ABS(F5-$L$2)*$L$5	=ABS(G5-$M$2)*$M$5	=IF(OR(Z5="",AA5=""),"",SUM(AB5:AD5))	=AGGREGATE(15,6,AE:AE,ROWS($AG$2:AG5))	=AGGREGATE(15,6,ROW(AE:AE)/(AE:AE=AG5),COUNTIF($AG$2:AG5,AG5))
57	Turkey Deli Slices	ingr	81	14,4	2,7	1,7														=IF(OR($I$2="",C6=$I$2),IF(C6="","",C6),"")	=IF(AND(D6<=$J$2,D6<>0),D6,"")	=ABS(E6-$K$2)*$K$5	=ABS(F6-$L$2)*$L$5	=ABS(G6-$M$2)*$M$5	=IF(OR(Z6="",AA6=""),"",SUM(AB6:AD6))	=AGGREGATE(15,6,AE:AE,ROWS($AG$2:AG6))	=AGGREGATE(15,6,ROW(AE:AE)/(AE:AE=AG6),COUNTIF($AG$2:AG6,AG6))
58	Brussel Sprout cook	veg	88	3,95	11,06	4,4														=IF(OR($I$2="",C7=$I$2),IF(C7="","",C7),"")	=IF(AND(D7<=$J$2,D7<>0),D7,"")	=ABS(E7-$K$2)*$K$5	=ABS(F7-$L$2)*$L$5	=ABS(G7-$M$2)*$M$5	=IF(OR(Z7="",AA7=""),"",SUM(AB7:AD7))	=AGGREGATE(15,6,AE:AE,ROWS($AG$2:AG7))	=AGGREGATE(15,6,ROW(AE:AE)/(AE:AE=AG7),COUNTIF($AG$2:AG7,AG7))
59	Pangasius poached	ingr	90	17	0	2														=IF(OR($I$2="",C8=$I$2),IF(C8="","",C8),"")	=IF(AND(D8<=$J$2,D8<>0),D8,"")	=ABS(E8-$K$2)*$K$5	=ABS(F8-$L$2)*$L$5	=ABS(G8-$M$2)*$M$5	=IF(OR(Z8="",AA8=""),"",SUM(AB8:AD8))	=AGGREGATE(15,6,AE:AE,ROWS($AG$2:AG8))	=AGGREGATE(15,6,ROW(AE:AE)/(AE:AE=AG8),COUNTIF($AG$2:AG8,AG8))
																				=IF(OR($I$2="",C9=$I$2),IF(C9="","",C9),"")	=IF(AND(D9<=$J$2,D9<>0),D9,"")	=ABS(E9-$K$2)*$K$5	=ABS(F9-$L$2)*$L$5	=ABS(G9-$M$2)*$M$5	=IF(OR(Z9="",AA9=""),"",SUM(AB9:AD9))	=AGGREGATE(15,6,AE:AE,ROWS($AG$2:AG9))	=AGGREGATE(15,6,ROW(AE:AE)/(AE:AE=AG9),1)

Respectively:

Range A:G: the partial data table you've provided;
Range I1:M2: the stats to be matched;
Range J4:M5: the weights (as in artificial neural networks) for the differences in stats;
Range P1:W4: the single-formula solution (the formula is in cell Q2);
Range Z1:AP9: the range-based solution;

Since your Excel is not in english, formulas won't work as they are now. You can use this subroutine to convert the values you've pasted into working formulas:

Sub SubValueToFormula()
    
    Dim RngCell As Range
    Dim RngSector As Range
    
    Set RngSector = Range("A1:AP9")
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For Each RngCell In RngSector.Cells
        
        RngCell.Value2 = RngCell.Formula
        
    Next
    
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
    
End Sub

Both methods follows the same logic:

pick the elements in the list accordingly to the category specified (cell I2); if no category is specified, all the elements are considered;
among them, pick those who have an equal or less amount of calories specified (cell J2);
among them, calculate the absolute difference in proteins, carbs and fats to the values specified (range K2:M2);
multiplies said differences by their corrisponding weights (as in an artificial neural network) specified (range K5:M5);
sum said multiplied differences;
among them, find the n lower value;
return the corrisponding row;

The range-based solution is easier to read. Then again, you can use this reduced version if you want to see the formula in action step by step:

=AGGREGATE(15,6,ROW($A$1:$A$9)/((ABS($E$1:$E$9-$K$2)*$K$5+ABS($F$1:$F$9-$L$2)*$L$5+ABS($G$1:$G$9-$M$2)*$M$5)/(($C$1:$C$9=$I$2)+($I$2=""))/($D$1:$D$9<=$J$2)/($D$1:$D$9>0)=AGGREGATE(15,6,(ABS($E$1:$E$9-$K$2)*$K$5+ABS($F$1:$F$9-$L$2)*$L$5+ABS($G$1:$G$9-$M$2)*$M$5)/(($C$1:$C$9=$I$2)+($I$2=""))/($D$1:$D$9<=$J$2)/($D$1:$D$9>0),ROWS($Q$2:Q2))),1)

Now, to be meticulous, the single-formula solution as it is can't report any draw correcly (it will return the first draw result n times). A single-formula solution if possible would be much more complicated with Excel 2016 (the one i have). To correcly report any draw, we might use a two-cell solution:

CALCUL	Name	Cat	Calories	Proteins	Carbs	Fats	Category	Calories	Proteins	Carbs	Fats		Index	Index corrected	Name	Cat	Calories	Proteins	Carbs	Fats
46	Orange	snack	90	2	22	0	ingr	90	10	5	0	=ROWS($P$2:P2)&"° suggestion"	`=AGGREGATE(15,6,ROW($A:$A)/((ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0)=AGGREGATE(15,6,(ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0),ROWS($Q$2:Q2))),1)`	`=IF(Q2=Q1,AGGREGATE(15,6,ROW($A:$A)/((ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0)=AGGREGATE(15,6,(ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0),ROWS($Q$2:Q2))),COUNTIF(Q$1:Q2,Q2)),Q2)`	=INDEX(B:B,$R2)	=INDEX(C:C,$R2)	=INDEX(D:D,$R2)	=INDEX(E:E,$R2)	=INDEX(F:F,$R2)	=INDEX(G:G,$R2)
50	Chobani Non-fat Plai	snack	90	16	6	0						=ROWS($P$2:P3)&"° suggestion"	`=AGGREGATE(15,6,ROW($A:$A)/((ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0)=AGGREGATE(15,6,(ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0),ROWS($Q$2:Q3))),1)`	`=IF(Q3=Q2,AGGREGATE(15,6,ROW($A:$A)/((ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0)=AGGREGATE(15,6,(ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0),ROWS($Q$2:Q3))),COUNTIF(Q$1:Q3,Q3)),Q3)`	=INDEX(B:B,$R3)	=INDEX(C:C,$R3)	=INDEX(D:D,$R3)	=INDEX(E:E,$R3)	=INDEX(F:F,$R3)	=INDEX(G:G,$R3)
50	Pear	snack	96	0,63	25,66	0,2			Protein	Carbs	Fat	=ROWS($P$2:P4)&"° suggestion"	`=AGGREGATE(15,6,ROW($A:$A)/((ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0)=AGGREGATE(15,6,(ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0),ROWS($Q$2:Q4))),1)`	`=IF(Q4=Q3,AGGREGATE(15,6,ROW($A:$A)/((ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0)=AGGREGATE(15,6,(ABS($E:$E-$K$2)$K$5+ABS($F:$F-$L$2)$L$5+ABS($G:$G-$M$2)$M$5)/(($C:$C=$I$2)+($I$2=""))/($D:$D<=$J$2)/($D:$D>0),ROWS($Q$2:Q4))),COUNTIF(Q$1:Q4,Q4)),Q4)`	=INDEX(B:B,$R4)	=INDEX(C:C,$R4)	=INDEX(D:D,$R4)	=INDEX(E:E,$R4)	=INDEX(F:F,$R4)	=INDEX(G:G,$R4)
54	Ezekiel bread	ingr	80	17	0	2		Weights	1	1	1
57	Turkey Deli Slices	ingr	81	14,4	2,7	1,7
58	Brussel Sprout cook	veg	88	17	0	2
59	Pangasius poached	ingr	90	17	0	2