How To Extract A Dynamic List Using Array Constants In Excel

In this tutorial we are going to learn how to extract a dynamic list by using Array Constants in our array formula in Excel.

To download the file and follow along, go to the link on my website at:

Do you need help with a formula or an Excel project?
Including a sample file is always helpful!

You can donate to my channel through any of the following:

PayPal:

Zelle or Venmo:

[jordanyoung]

Great video! You don't know how much time this saved me!

[chipper]

For the TEXTJOIN Formula, you just need to add quotes to the formula to have it add quotes around each value. There is no need to manually go through and add the quotes later. So your Formula would look like =TEXJOIN(", ", 1, """" & G2:G9 & """") That is 4 quotes marks in a row before and after the range and this will put quotes around each value for you.

[wayneedmondson]

Hi Mike.. interesting challenge. I solved it a couple of ways, as follows.. the first 2 require Office 365 and FILTER.. the other two are conventional and require CSE (I think) in legacy EXCEL:
=FILTER(A2:B101, ISNUMBER(MATCH(B2:B101, E11:E13, 0))) - spilled array
=FILTER(A2:B101, COUNTIF(E11:E13, B2:B101)) - spilled array
=IFERROR(INDEX(A$2:A$101, AGGREGATE(15, 6, 1/ISNUMBER(MATCH($B$2:$B$101, $E$11:$E$13, 0))*(ROW($A$2:$A$101)-ROW($A$2)+1), ROWS(I$2:I2))), "") - copy down and right
=IFERROR(INDEX(A$2:A$101, SMALL(IF(ISNUMBER(MATCH($B$2:$B$101, $E$11:$E$13, 0)), ROW($A$2:$A$101)-ROW($A$2)+1), ROWS(I$2:I2))), "") - copy down and right
I didn't use array constants.. just pointed to the range on the worksheet with the variables.. i.e. E11:E13. This gives the flexibility to change the variables vs. hard coding them in a formula or named range. Never the less, interesting to learn all the ways to coax EXCEL to produce results you want to see. Thanks for the lesson and the tips. Thumbs up!!