Having trouble pulling the correct values based on several criteria

[carrollkm]

Hi All!

I'm having a little bit of trouble creating a formula for my situation. I am using this for a weight training template for clients of mine. On one sheet, I have a list of exercises in the A Column. In the first row I have a list of set and repetition possibilities. On the other sheet I have daily exercise prescription with drop down menus for both exercise selection and the set and rep possibility. I would like the heading titled "1RM" on the 2nd sheet to return the correct value based on the exercise choice and the set and repetition choice. I'm attaching a sample sheet which has the problem in it.

The actual values I would like to be returned are from F2:AW300 in Sheet1 and the formula in Sheet 2 D6

The criteria I would like:

Sheet1 A2:A300=Sheet2 A6

Sheet1 F1:AW1=Sheet2 C6

I would really appreciate your all's help with my problem and thank you to anyone who takes the time to lend a hand!

Thanks,

Kevin

[Jean.P28]

Can this help you

[carrollkm]

Jean-Paul,

Thank you for your reply! Yes this is helpful, it seems to work! I was curious what the words in the formula indicate? "DataTemplate" and "ValSetXRep" for instance. Are these corresponding to range values?

[Jean.P28]

Name range, they automatically expand as data is being attached.


How note that my native language is not English

[FDibbins]

Jean, just an observation - regular named ranges do not expand as you add data. You used Dynamic range names, and yes, these do adjust for extra data - nice trick

However, in this case, this will work just as well, and will cater for growing rows, without the need for a volatile named range...
=IFERROR(INDEX(Sheet1!$A:$AW,MATCH(Sheet2!$A6,Sheet1!$A:$A,0),MATCH(Sheet2!$C6,Sheet1!$A$1:$AW$1,0)),"")

copied down