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,
Not sure about formulas with rows/columns, i'm back to AGGREGATE. The idea is to take instead of single column the range from D to M columns and apply filter to use every first/second column, like for 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)Had no time to play with other calculations, shall be similar. Please see the yellow cell in attached.
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
Sergei,
I found an issues that I was hoping you could help correct. What i want to do is find the highest rep and weight total and second highest rep and weight total.For example, currently the Highest Rep and Weight is showing:
Highest rep: 6
Highest Weight: 65
Second Highest rep: 8
Second Highest Weight: 55What it should be is:
Highest rep: 10
Highest Weight: 50
Second Highest rep: 8
Second Highest Weight: 5510 * 50 = 500
8*55 = 440From there the 1RM is calculated in the Notes sheet based on the results. Sorry to be a pain but would this be difficult to correct?
I have updated the spreadsheet in hope to provide greater detail as to what i am trying to do, changes made on the Tracking sheet for simplification. Eventually this data would be moved to the notes sheet.
Hi David,
Here are update formulas, first two are array ones
Set Formula Highest Rep to weight {=INDEX(Tracking!$D$3:$L$4, MATCH('1RM'!$C$1,Tracking!$C$3:$C$4,0),MATCH(LARGE(OFFSET(Tracking!$D$2,MATCH('1RM'!$C$1,Tracking!$C$3:$C$4,0),0,,9)*OFFSET(Tracking!$D$2,MATCH('1RM'!$C$1,Tracking!$C$3:$C$4,0),1,,9)*{1,0,1,0,1,0,1,0,1},1),OFFSET(Tracking!$D$2,MATCH('1RM'!$C$1,Tracking!$C$3:$C$4,0),0,,9)*OFFSET(Tracking!$D$2,MATCH('1RM'!$C$1,Tracking!$C$3:$C$4,0),1,,9)*{1,0,1,0,1,0,1,0,1},0))} Second Highest Rep to weight {=INDEX(Tracking!$D$3:$L$4, MATCH('1RM'!$C$1,Tracking!$C$3:$C$4,0),MATCH(LARGE(OFFSET(Tracking!$D$2,MATCH('1RM'!$C$1,Tracking!$C$3:$C$4,0),0,,9)*OFFSET(Tracking!$D$2,MATCH('1RM'!$C$1,Tracking!$C$3:$C$4,0),1,,9)*{1,0,1,0,1,0,1,0,1},2),OFFSET(Tracking!$D$2,MATCH('1RM'!$C$1,Tracking!$C$3:$C$4,0),0,,9)*OFFSET(Tracking!$D$2,MATCH('1RM'!$C$1,Tracking!$C$3:$C$4,0),1,,9)*{1,0,1,0,1,0,1,0,1},0))} Rep Difference =10-(G26-G27) Highest Weight =AGGREGATE(14,6,1/(Tracking!$C$3:$C$4='1RM'!$C$1)*Tracking!$D$3:$L$4*Tracking!$E$3:$M$4*{1,0,1,0,1,0,1,0,1,0},1) Highest Weight per Rep =G29/G26 Second Highest Weight =AGGREGATE(14,6,1/(Tracking!$C$3:$C$4='1RM'!$C$1)*Tracking!$D$3:$L$4*Tracking!$E$3:$M$4*{1,0,1,0,1,0,1,0,1,0},2) Second Highest Weight per Rep =G31/G27 Weight Difference =G29-G31 KG Per Rep =G33/G28They are in attached file below the previous variant. Perhaps first two could be simplified, not sure right now
Sergei,
This is great, i dont want to ask to much of you and so as long as it is working I am very greatful. It all looks to be working to me.
I just need to expand the forumulas to include alld the empty cells below to include future data entries.
You are very proficient at exel and if i wore a hat it would go off to you :)
David,
So you find highest one as highest (KG*REP), not as highest KG.
Okay, i'll try
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.