Forum Discussion
Using Excel lookup based on multiple criteria
Hi David,
please see attached for the sample solution. For the second highest rep I used the following formula (array entered):
{=INDEX($E$2:$E$500,LARGE(IF($C$2:$C$500=K1,ROW($C$2:$C$500)-ROW(INDEX($C$2:$C$500,1,1))+1),2))}
Similarily, for the second highest weight I used
{=INDEX($F$2:$F$500,LARGE(IF($C$2:$C$500=K1,ROW($C$2:$C$500)-ROW(INDEX($C$2:$C$500,1,1))+1),2))}
Hope this helps
Regards
Yury
Hi David,
I updated the rest of formulas with how i understood that logic
Set Formula
Highest Rep =INDEX(Tracking!$A$3:$M$20,MATCH('1RM'!$C$1,Tracking!$C$3:$C$20,0),MATCH(G20,OFFSET(Tracking!$A$3,MATCH('1RM'!$C$1,Tracking!$C$3:$C$20,0)-1,0,1,13),0)-1)
Second Highest Rep =INDEX(Tracking!$A$3:$M$20,MATCH('1RM'!$C$1,Tracking!$C$3:$C$20,0),MATCH(G21,OFFSET(Tracking!$A$3,MATCH('1RM'!$C$1,Tracking!$C$3:$C$20,0)-1,0,1,13),0)-1)
Rep Difference =IF(G16>G15,0,G15-G16)
Weight Difference =IF(G21>G20,0,G20-G21)
KG per Rep =IF(HighestRep-SecondHighestRep=0,WeightDifference/1,IF(G17<=0,WeightDifference/(SecondHighestRep-HighestRep),G18/G17))
Highest Weight =AGGREGATE(14,6,1/(Tracking!$C$3:$C$20='1RM'!$C$1)*Tracking!$D$3:$M$20*{0,1,0,1,0,1,0,1,0,1},1)
Second Higest Weight =AGGREGATE(14,6,1/(Tracking!$C$3:$C$20='1RM'!$C$1)*Tracking!$D$3:$M$20*{0,1,0,1,0,1,0,1,0,1},2)Please note, in 1RM i took cell C1 to find the record (not B1 with incorrect list of values). No one array formula above.
File is attached
Genius,
Thanks so much honestly, this was over my head.
The sheet seems to be responding much faster without the Array formulas too.
David, you are welcome.
Okay, i'll try to comment the formulas, perhaps more close to weekend.
Array formulas are quite powerful but very slow on large ranges. In such cases if possible to avoid them - better to avoid.