Append arrays in Excel using FILTERXML

This is the problem statement:

You have 3 arrays of data in Column B,C,D for different regions and you want to append them.

We can write the following formula in F3:

=FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,B3:B12,C3:C12,D3:D12)&"</m></x>","//m")

The result will be all the values appended together.

Let us take another example.

This is the problem statement: We want to get a list of all transactions alongwith the sum.

This is the formula in F3:

=LET(mycalc,B3:D4,
FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,mycalc,SUM(mycalc))&"/m></x>","//m"))

The result will be like this, with an automatic SUM at the end.

The advantage here is that the SUM is not an independent formula — and the entire result including SUM is part of a single formula.

Let us look at the implementation in Dynamic Arrays as well.

This is the problem statement: We want to get the Top N values from the 3 Regions in a List. The user can select the Top N in J2.

This is the result:

Formula in F5:

=LARGE(B5:B14,ROW(INDIRECT("1:"&$J$2)))

Formula in J5:

=FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,F5#,G5#,H5#)&"</m></x>","//m")

Conclusion:

There are immense possibilities of FILTERXML with Dynamic Arrays, especially in any advanced spreadsheet modeling / financial modeling.