Dynamic Output Based on Dropdown Selection With Corresponding Range V2

[Relic24]

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.