Using Excel lookup based on multiple criteria

Hi All, Please help me as i am frustrated to the point of giving up. I am trying to use excel to lookup a value, based on that value look with another cell and find the highest number associated...

Example.xlsx24 KB

Formulas & Functions

Need Help

Yury Tokarev
Sep 04, 2017
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

Example Nth Lookup.xlsx24 KB

SergeiBaklan

Diamond Contributor

Sep 26, 2017

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/G28

They are in attached file below the previous variant. Perhaps first two could be simplified, not sure right now

Routine_ (3).xlsx25 KB

David Attard

Copper Contributor

Sep 27, 2017

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 :)