In Excel using VBA or formula, how to generate in single cell a comma-separated list based on complex criteria?

Question

I am looking for an Excel formula or VBA solution that generates the result in Table2 column Party based on the data in Table1 and the inputs in Table2 column N. The inputs in column N of Table2 are numbers corresponding to the order of the rows of data in Table1. Party in Table2 should return ShortName from Table, but if ShortName is blank, then Party in Table2 should return Name. Thank you for your help.

Table1

Name	ShortName
Jon Doe	Doe
Robert Smith
Susan Miller	SM
Donald Duck
Micky Mouse	The Mouse
Kog Enterprises, Inc.	Kog
Mechanical, Inc.

Table2

N	Party
1,2,3	Doe,Robert Smith,SM
4	Donald Duck
3,5,6	SM,The Mouse,Kog
2,3,5,6,7	Robert Smith,SM,The Mouse,Kog,Mechanical, Inc.

The formula below works, but only for ShortName; it does not check ShortName and, if blank, default to Name:

=TEXTJOIN(",",TRUE,
INDEX(FILTER(Table1[ShortName],Table1[ShortName]<>""),
MATCH(NUMBERVALUE(TEXTSPLIT([@N2],,",",TRUE,1)),
ROW(Table1[ShortName])-ROW(Table1[[#Headers],[ShortName]]),
0)))

The formula below correctly switches between Name and ShortName, but cannot handle multiple comma-separated values in Table2 column N:

IF(INDEX(FILTER(CHOOSE(IF(LOWER(LEFT([@N2],1))="-",1,2),Table3[[Name]:[ShortName]],Table1[[Name]:[ShortName]]),CHOOSE(IF(LOWER(LEFT([@N2],1))="-",1,2),Table3[Name],Table1[Name])<>""),SUBSTITUTE(LOWER([@N2]),"-",""),3)=0,INDEX(FILTER(CHOOSE(IF(LOWER(LEFT([@N2],1))="-",1,2),Table3[[Name]:[ShortName]],Table1[[Name]:[ShortName]]),CHOOSE(IF(LOWER(LEFT([@N2],1))="-",1,2),Table3[Name],Table1[Name])<>""),SUBSTITUTE(LOWER([@N2]),"-",""),1),INDEX(FILTER(CHOOSE(IF(LOWER(LEFT([@N2],1))="-",1,2),Table3[[Name]:[ShortName]],Table1[[Name]:[ShortName]]),CHOOSE(IF(LOWER(LEFT([@N2],1))="-",1,2),Table3[Name],Table1[Name])<>""),SUBSTITUTE(LOWER([@N2]),"-",""),3))

The formula above also has other features not discussed here to help keep this post as simple as possible.

Answer 1

Another alternative using ARRAYTOTEXT() & XLOOKUP()

---

• Formula used in cell E2

=LET(
    α, Table1[ShortName],
    σ, Table1[Name],
    ARRAYTOTEXT(
        XLOOKUP(
            TEXTSPLIT(
                [@N],
                ,
                ","
            ) * 1,
            ROW(α) - 1,
            IF(α = "", σ, α)
        )
    )
)

---

Note: Since you have mentioned in your post that you have Table1 & Table2 hence spill array formulas won't work with Structured References, therefore the above formula needs to be filled down or it will automatically fill down because of Table Structured behaviors.

---

Answer 2

Match Data by Indexes

• The formula in the 1st row of the Party column:

=IFERROR(TEXTJOIN(", ",,INDEX(IF(
    LEN(Table2[ShortName])>0,Table2[ShortName],Table2[Name]),
        TEXTSPLIT([@N],","))),"")

• Excel (structured) tables do not support spilling formulas. Clear the contents of the Party column before entering the formula and the whole column will be populated.

Answer 3

Assuming no version constraints as per your tags, the following seems to produce what you describe:

F2: =TEXTJOIN(
    ", ",
    TRUE,
    LET(
        r, --TEXTSPLIT(E2, ","),
        f, CHOOSEROWS(Table18, r),
        IF(TAKE(f, , -1) = "", TAKE(f, , 1), TAKE(f, , -1))
    )
)

and fill down

Or as a single formula entered only in F2:

=BYROW(
    E2:E5,
    LAMBDA(arr,
        TEXTJOIN(
            ", ",
            TRUE,
            LET(
                r, --TEXTSPLIT(arr, ","),
                f, CHOOSEROWS(Table18, r),
                IF(TAKE(f, , -1) = "", TAKE(f, , 1), TAKE(f, , -1))
            )
        )
    )
)

You can also avoid filling down by setting your output in a Table. By entering the formula in the first row of the Party column, the formula will fill down automagically.