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

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

Table2

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.

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.

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.

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.