Forum Discussion
Solved
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 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.
I have attached the spreadsheet for additonal reference.
Thanks in advance.
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
- "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 HI All,
Out of interest would it be possible to do the lookup horizontally instead of vertically?See attached as an example.
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
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.
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.
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.
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
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
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.
