Forum Discussion
Dynamic Output Based on Dropdown Selection With Corresponding Range V2
Hi LouisDeconinck ,
Apologies for the delay.
I have included the workbook with two sheets: one containing V1 in sheet 1 and the V2 layout in sheet 2. Additionally, I've included annotations and text boxes to clarify the specific issues I need assistance with in sheet 2.
Relic24 Just tested things and they seem to be working as intended with the current formula. You put the formula in cell J6, press enter. Then you copy cell J6 and paste in cells J9, J12 and J15.
The current formula uses an expanding reference, which means that you need to use this copy-paste technique for the ranges to automatically update.
=IFERROR(IF(B6="","",INDEX($G$3:I5,SEQUENCE(3,,XLOOKUP(B6,B3:B$3,SEQUENCE(ROWS(B$3:B3)),"N/A",0,-1)),SEQUENCE(,3))), "")
Those parts I bolded are expanding range references. The starting point stays fixed (through the $'s) while the ending point shifts down automatically to the following rows as you pull down / copy down. The -1 as the search mode argument in the XLOOKUP makes sure to search from last to first, meaning that the results are always from the last match.
Because of the copy-paste, the references will automatically change. E.g. this is how the formula will look like in cell J15:
=IFERROR(IF(B15="","",INDEX($G$3:I14,SEQUENCE(3,,XLOOKUP(B15,B$3:B12,SEQUENCE(ROWS(B$3:B12)),"N/A",0,-1)),SEQUENCE(,3))), "")
Optional tip if this was helpful: [link removed by admin]