Filterxml function

[anandvarma]

I am trying to append two sheets using formulas as described from the following video

i am using microsoft 365

the formula used is

=INDEX(FILTERXML("<Tom><Jerry>"&TEXTJOIN("</Jerry><Jerry>",TRUE,Table1)&"</Jerry></Tom>","//Jerry"),SEQUENCE(COUNTA(Table1[Item]),10))
this is working
whereas if i add second range
=INDEX(FILTERXML("<Tom><Jerry>"&TEXTJOIN("</Jerry><Jerry>",TRUE,Table1,Table2)&"</Jerry></Tom>","//Jerry"),SEQUENCE(COUNTA(Table1[Item],Table2[Item]),10))
if am getting value error

Table1 and Table2 are defined as tables in sheet 1 and sheet 2

what i am missing in the formual

anandvarma

 

[GraH]

Hi if you are on 365 then appending data is way simpler with Power Query.
Google append tables with Power Query. It's a walk in the park.

 

[ISY]

Hi

Use TEXTJOIN

=FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,A1:A12,C4:C8)&"</s></t>","//s")