Multi-Function Equation Using LOOKUP, VLOOKUP, MATCH, INDEX?

Hello All,

This is hard to articulate but here goes……I need a formula that can do the following:

Data Inputs with Examples from Sheet 1:

Body Weight: 166.5 (Already have this LOOKUP formula that selects the most current weight from a series of weigh-ins)
Exercise Name: Incline Push Up
Exercise Height #: 3 (= 20 inches)
Unit of Measure Codes: W or T or J or B or P (All 5 of these designate the exercise is a bodyweight exercise instructing the formula to either utilize body weight or a % of it; the only remaining code, R, not associated with a body weight exercise would allow indirect input of a barbell weight referenced from another cell on Sheet 1)

From an Array in Sheet 2:

I have already plugged in all the data. Key is that formula must recognize the exercise name on sheet 1

Formula Outputs in Sheet 1:

Exercise Weight: 82 (166.5 x 49%; which is already calculated in the array on Sheet 2)

Thank you,

Patrick

mcmahobt,

Thanks for your quick solution! I cut and paste into my spreadsheet and after updating the sheet names from the generic "Sheet1" etc., I got a calculation error. Since this formula was entered into cell D9 on Sheet1 according to your instruction, I deleted that sheet name since it was already on that sheet - was that the correct thing to do?

However cell D9 is blank, so shouldn't this formula go in cell C14 where this calculation should occur?

My Formula Changes:
=INDEX('Grading-Tables'!B$6:B$18,MATCH($C$9,INDIRECT('Grading-Tables'!&ADDRESS(6,7+IF($B$1="men",MATCH($B$2,'Grading-Tables'!$H$5:$M$5,0),MATCH($B$2,'Grading-Tables'!O5:T5,0)))&":"&ADDRESS(18,7+IF($B$1="men",MATCH($B$2,'Grading-Tables'!$H$5:$M$5,0),MATCH($B$2,'Grading-Tables'!$O$5:$T$5,0)))),0))

Your Original Formula:
=INDEX(Sheet2!B$6:B$18,MATCH($C$9,INDIRECT("Sheet2!"&ADDRESS(6,7+IF(Sheet1!$B$1="men",MATCH(Sheet1!$B$2,Sheet2!$H$5:$M$5,0),MATCH(Sheet1!$B$2,Sheet2!$O$5:$T$5,0)))&":"&ADDRESS(18,7+IF(Sheet1!$B$1="men",MATCH(Sheet1!$B$2,Sheet2!$H$5:$M$5,0),MATCH(Sheet1!$B$2,Sheet2!$O$5:$T$5,0)))),0))

Thanks much,

Patrick

mcmahobt,

The actual file is attached.

All of the involved cells, rows, columns are highlighted in orange to help locate them quicker:

In CIRCUIT-BUILDER sheet:

Parts of Row 55

Parts of Row 391

Parts of Row 560

In GRADING-TABLES sheet:

Array E36:H57

Thank you!

Patrick

Sheet 4 is not related to this formula but at first glance it appears that way. That's another complicated formula somewhat sorted out. By sending you the entire spreadsheet, it is easier to dial in the formula with the original cell coordinates. Sorry for the confusion!

This formula was born out of the necessity to improve the following.

Speeding up data entry by eliminating several steps which meant...

Automatically calculating what % of their own body weight they are lifting with certain exercises rather than manually figuring it.

Standardizing this % body weight so that it can be used to convert pounds lifted divided by time divided by heart rate into points

So in the the Formulaic Exercise Adjustments section, this % body weight will display there; it will show up in the Previous Session Results section; And finally in the Session Recording Templates on Sheet 3.

mcmahobt and Vikas_Gautam,

My apologies to both of you as I just realized that I had originally sent the wrong spreadsheet So sorry for the mix up! The correct one is attached.

Sincerely,

Patrick

mcmahobt,

It works! However, it is not affected by anything or nothing in Cell E14 in the Circuit-Builder Sheet. So whether it is blank or any of the other code letters are used = same result. It should......

= Cell G8 when Cell E14 = blank

= 82 when Cell E14 = W or T or J or B or P (All 5 of these designate the exercise is a body weight exercise instructing the formula to either utilize body weight or a % of it; the only remaining code, R, not associated with a body weight exercise would allow indirect input of a barbell weight referenced from another cell on Sheet 1)

Patrick

That did it That unnamed sheet 1 cell reference will be G8 so that will work as you constructed it for that as well. Many thanks for all your help. I would have never figured this out on my own.


PS To help me understand the syntax better, why are brackets,{}, used in a formula?