Index Match Match for a small table lookup

It's probably going to be one of these:

=INDEX(AN2:AP7,MATCH(AW5,AM2:AM7,0),MATCH(AW6,AN1:AP1,0))
if AW6 is one of the values in AN2:AP7

or

=INDEX(AN1:AP1,MATCH(AW6,INDEX(AN2:AP7,MATCH(AW5,AM2:AM7,0),),0))
if AW6 is one of the values in AN1:AP1 (Lower, Middle, or Upper)

Edit: Other way around...

slvrbktom, I have another approach in mind, but I have a question first. You state 8 tables and the upload reflects that. However I find 9 unique exercises. Which of those apply to the 8 tables?

Squat

Overhead Press

Deadlift

Bench Press

Accessory

Glute Bridge

DB RDL

Skull Crushers

McGill Big 3

If each of the tables had their exercise in the header like AN1 "Squat" I could show what I mean.

The reason I ask there is a seldom used optional 4th argument in INDEX area_num. It works well in situations like this. With 8 tables the setup is lightly tedious, but once it's done it's done. I doesn't need maintenance.

The drop downs in the upload don't give a clue concerning exercises except that they reference another source not included.

I could make up a list and demo file if you'd like.

Let me know.

I'll attempt to describe how this works. You may want to have the attached open. It could help in following this wall of text. As stated above the setup is a little tedious, but once completed should be maintenance free.

The help file explains two syntax forms of INDEX. They are the Array form and the Reference form. The attached uses the Reference form.
It is:

INDEX(reference, row_num, [column_num], [area_num])

Before beginning find this named rang definition table in the attached BF1:BG4

Exer_Tables	=Training_Plan!$AN$3:$AP$8,Training_Plan!$AS$3:$AU$8,Training_Plan!$AN$12:$AP$17,Training_Plan!$AS$12:$AU$17,Training_Plan!$AN$21:$AP$26,Training_Plan!$AS$21:$AU$26,Training_Plan!$AN$30:$AP$35,Training_Plan!$AS$30:$AU$35
Exer_Name	=Training_Plan!$AZ$1:$AZ$8
Exer_Category	=Training_Plan!$BA$1:$BA$6
Exer_Target	=Training_Plan!$BB$1:$BB$3

Take note of Exer_Tables and its non contiguous range. It will be the first argument in INDEX. Each section in the definition must be separated by a comma. These are the areas and INDEX understands them to be numbered left to right 1 to 8. They are returned by a MATCH function in the area_num argument. Establishing the order of the area_nums is the most tedious step. With Name Manager open simply select the first section, followed by a comma, select the next section, followed by a comma ... repeat and rinse. I did it selecting tables left to right and then down, left to right and down ... etc. etc.

It is good that you have consistency in the row and column headers of each table. This means that no matter what area_num (and therefore exercise/table) is the active area_num all row and column arguments passed to INDEX will apply to and only apply to the current area_num.

Please also take note that the area_nums/Exercises are in exactly the same order as Exer_Names in AZ1:AZ8. Exer_Names serves as the DV list for the drop downs and the lookup_array in the area_num MATCH function. The matching order assures MATCH will return the correct Table.

The rest of the definitions also serve as DV lists for the remaining drop downs and as lookup_array arguments for the remaining MATCH function calls.

The final formula in B18:

Formula:

Please Login or Register  to view this content.

Copy that cell and paste into cells B21, B24, B27 ... etc.

I put some fake data into the remaining seven tables so you can experiment and confirm that this works properly.

Does this do what you hoped for?

You are welcome. Glad it does the job. Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.