How to add item in the bottom of array already generated with INDEX

[MBS]

Hi all,

Refer attached sheet. Solution for Excel 2013 is expected. (without Dynamic Array)
I want to add "region" at the bottom of every list appeared.
I think, My question can be understood as you go through attached sheet.
I will use this in drop down list.

Thanks All.

 

[bosco_yip]

Does this what you are expected?

1] In D22:D33, I used an Index+Match copied down formula+Conditional formatting to mask duplicates

2] Please see my attachment.

 

[Peter Bartholomew]

Of course, you could always dump Office 2013 and move to a more straightforward programming environment!

= LET(
      dropdown,  XLOOKUP(selectedDDName,dropdownHeader,dropdowns),
      nonblanks, FILTER(dropdown, dropdown<>""),
      VSTACK(nonblanks, "Region")
  )

or, if you prefer to avoid formulae showing their content on your spreadsheet,

= Dropdownλ(selectedDDName)

Then again, you may not be so fortunate as to have the option

 

[MBS]

Does this what you are expected?

1] In D22:D33, I used an Index+Match copied down formula+Conditional formatting to mask duplicates

2] Please see my attachment.

View attachment 81979

Thanks bosco_yip,
For Sure, this is an addition to my knowledge. I just wander can we add "Region"only once, unlike with dynamic arrays.

 

[bosco_yip]

It is possible & easier if you have Excel2019 and up version.

Or if you have Textjoin function.

Then,

What is your Excel version using?

Are you using Excel 2013?

Regards