Dynamic Output Based on Dropdown Selection With Corresponding Range V2

Initial problem

Creating a dynamic output based on the selection from dropdowns in cells B19 and B22 when the same criterion was chosen. In simpler terms, when one range is filled, its content is copied to another range, when the corresponding dropdown menu's criteria are met.

The context is an exercise program, in which the athletes need a way to track their previous performance measured by reps, sets, and RPE the last time they used a specific exercise.

See picture below for reference.

Sk?rmbillede 2023-12-09 kl. 20.58.52.png

This was solved by:

=IFERROR(IF(B22="","",INDEX(O$19:Q21,SEQUENCE(3,,XLOOKUP(B22,B$19:B19,SEQUENCE(ROWS(B$19:B19)),"N/A",0,-1)),SEQUENCE(,3))),"")


V2

I want to expand this functionality to five different dropdowns, dynamically.

Corresponding range = green cells in the picture below

Dropdown list B19 with corresponding range =O19:Q21

Dropdown list B22 with corresponding range =O22:Q24

Dropdown list B25 with corresponding range =O25:R27

Dropdown list B28 with corresponding range =O28:Q30

Dropdown list B31 with corresponding range =O31:Q33



The problem lies in creating a dynamic Excel formula that not only searches for the most recent occurrence of the selected option from any dropdown but also considers multiple dropdowns meeting the same criteria.

It should be configured such that when an option is chosen from a dropdown (for instance, in cell B25), it should search for the last instance where that same option was selected in the previous dropdowns (such as B19 and B22).
Then, it should display the data associated with the most recent selection (in this case, the data from the range O22:Q24 for B22) into the new corresponding range aligned with the current selection (which would be U25:W27 for B25).

Here's the layout, five dropdowns all meeting the same criteria. Green-colored cells are the range of which the performance is inputted while the purple-colored cells are for the records of previous performance.

Sk?rmbillede 2023-12-09 kl. 21.01.34.png

I also attached a sample file with the layout.

I'm working in Excel on Mac, Excel 365, Version: 16.79.2. The solution should be tailored to fit these specifications.

Administrative Note:

Welcome to the forum.

Is your forum profile showing the Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

Your sample workbook is far too pared back - most of what we would need to see is missing - no drop-downs, no formulae, no expected results manually mocked up. Also your screenshots are too small - add the annotations to the sample workbook instead.

Sorry to press on this, but I asked for annotations to be added to the sample workbook. Some signposting is necessary - I cannot read the screenshots.

There is a screenshot in your first post with annotations - those annotations should be in the sample workbook.

Your concept is completely alien to me - you need to hold our hands in explaining it to us.

This proposal employs a helper column which may be moved and/or hidden for aesthetic purposes.
1. The helper column (M) is populated using: =IF(B3<>"",B3&1,SUBSTITUTE(M2,IF(RIGHT(M2,1)+0=1,1,2),RIGHT(M2,1)+1))
2. The cells in the previous performance section are populated using: =IFERROR(INDEX(G$3:G3,AGGREGATE(14,6,(ROW(G$3:G3)-ROW(G$2))/($M$3:$M3=$M6),1)),"")
Let us know if you have any questions.