SOLVED

# Using Excel lookup based on multiple criteria

Highlighted
Occasional Contributor

# Using Excel lookup based on multiple criteria

Hi All,

I am trying to use excel to lookup a value, based on that value look with another cell and find the highest number associated to that said value, and output the value froma a cell adjacent to it.

Eg.

 Week Date Workout Set Rep KG Total Week 1 4/09/2017 Dead lift 1 12 20 240 Week 1 4/09/2017 One Arm Floor Press 1 12 15 180 Week 1 4/09/2017 Barbell Shrug (Behind Back) 1 12 8.75 105 Week 1 4/09/2017 Medow Row 1 12 20 240 Week 1 4/09/2017 Skull Crushers 1 12 10 120 Week 1 4/09/2017 Side bends 1 12 20 240 Week 1 4/09/2017 Lying Doubble Leg Raise 1 12 0 0 Week 1 4/09/2017 Lunges 1 12 0 0 Week 1 4/09/2017 Straight Arm Pulldown 1 12 10.2 122.4 Week 1 4/09/2017 Face Pull 1 6 13.6 81.6 Week 1 4/09/2017 Machine Rope Hammer Curls 1 12 6.8 81.6 Week 1 4/09/2017 Reverse Cable Side Bends 1 12 10.2 122.4 Week 1 4/09/2017 Squats 1 12 25 300 Week 1 4/09/2017 Single arm Lat Pulldowns 1 11 10.2 112.2 Week 1 4/09/2017 Bench Press (Supinated Grip) 1 11 6 66 Week 1 4/09/2017 Landmine Later Raise 1 12 8.75 105 Week 1 4/09/2017 Incline Tricep Extension 1 8 0 0 Week 1 4/09/2017 Incline Dumbell Curls 1 12 10 120 Week 1 4/09/2017 Pushups 1 12 10 120 Week 2 4/09/2017 Dead lift 1 10 50 500

Cell 1: Define a lookup value: E.g `Dead Lift`

Cell 2: Based on the lookup value `Dead Lift` find the heaviest lift and return the KG.

Cell 3: Based on the heaviest lift KG return the adjacent `Rep` cell

Cell 4: Based on the lookup value `Dead lift` find the second heviest lift and return the KG

Cell 5: Based on the second heviest lift  in KG return the adjacent `Rep` cell.

I feel like this should be easy but i am having to much trouble trying to do this.

17 Replies
Highlighted
Best Response confirmed by David Attard (Occasional Contributor)
Solution

# Re: 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

Highlighted

# Re: Using Excel lookup based on multiple criteria

Another variant is without array formulas

First largest

=AGGREGATE(14,6,(1/(\$C\$2:\$C\$500=\$K\$1))*(E2:E500),1)

Second largest

=AGGREGATE(14,6,(1/(\$C\$2:\$C\$500=\$K\$1))*(E2:E500),2)

etc.

Highlighted

# Re: Using Excel lookup based on multiple criteria

Hi Sergei,

Just letting you know your solution works however it was not quite what i was trying to achive. The result would sometimes be the wrong value and so i went with the array solution.

I appreciate you taking the time to assist me though.

Highlighted

# Re: Using Excel lookup based on multiple criteria

Yury = Champion.

Thanks mate, this was driving me crazy.

Microsoft should make this into a function, I can see this being a common scenario and do a simple google shows im not the only one asking.

Thanks again.

Highlighted

# Re: Using Excel lookup based on multiple criteria

Hi David,

No problem, in my formula weight filter was missed. In general you may add as many filters as needed adding the multiplier. Formulas are

=AGGREGATE(14,6,(1/(\$C\$2:\$C\$500=\$K\$1))*(E2:E500)*(\$F2:\$F\$500=\$L\$9),1)
=AGGREGATE(14,6,(1/(\$C\$2:\$C\$500=\$K\$1))*(E2:E500)*(\$F2:\$F\$500=\$L\$10),1)
=L4-L5
=L9-L10
=L7/L6
=AGGREGATE(14,6,(1/(\$C\$2:\$C\$500=\$K\$1))*(F2:F500),1)
=AGGREGATE(14,6,(1/(\$C\$2:\$C\$500=\$K\$1))*(F2:F500),2)

and in attached file

Highlighted

# Re: Using Excel lookup based on multiple criteria

HI All,
Out of interest would it be possible to do the lookup horizontally instead of vertically?

See attached as an example.

Highlighted

# Re: Using Excel lookup based on multiple criteria

Hi David,

In general everything is possible. Your sample is empty except errors in the cells. If you provide some sample with numbers and where you'd like to have which result that will be easier - bit hard to recover the logic of your model without the knowledge of what's that about.

Thank you

Highlighted

# Re: Using Excel lookup based on multiple criteria

Hi Sergei,

I have updated the spreadsheet to provide more details.

TABS:

Tracking - Data that will be recording from my workouts

1RM - Calculator for 1RM based on the number in `Notes`

Notes - This is where the fomulas are to get the highes rep, second hights rep and weights from my very first post. I used the fomulas from Yuri to get this all working. Happy to is it working with your formulas.

I was curious if it was possible to do it horizontaly vs vertially to save having to type the exercise 4 - 5 for each workout. Seemed more logical with approach. Open to any other suggestion, i am trying to simplfy the repetiviness. I still have not work out a way from not have to type week 1, week 2 etc and the date the exercise was done, could do a macro bust seems like overkill to me.

Highlighted

# Re: Using Excel lookup based on multiple criteria

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.

Highlighted

# Re: Using Excel lookup based on multiple criteria

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

Highlighted

# Re: Using Excel lookup based on multiple criteria

Genius,

Thanks so much honestly, this was over my head.

The sheet seems to be responding much faster without the Array formulas too.

Highlighted

# Re: Using Excel lookup based on multiple criteria

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.

Highlighted

# Re: Using Excel lookup based on multiple criteria

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

What it should be is:
Highest rep: 10
Highest Weight: 50
Second Highest rep: 8
Second Highest Weight: 55

10 * 50 = 500
8*55 = 440

From 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.

Highlighted

# Re: Using Excel lookup based on multiple criteria

David,

So you find highest one as highest (KG*REP), not as highest KG.

Okay, i'll try

Highlighted

# Re: Using Excel lookup based on multiple criteria

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

Highlighted

# Re: Using Excel lookup based on multiple criteria

"I feel like this should be easy but i am having to much trouble trying to do this. "

while as a human we can quickly get to pick this up there is a lot going on as it is you have 5 Cell rules with a lot of implied logic "the second heaviest left" for example.
and as you can see in some of the "code" supplied this is what Excel has to do to get the result you need.

I think you should consider other tools (excel is a spreadsheet not a database) have a try at ACCESS
Highlighted

# Re: Using Excel lookup based on multiple criteria

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