FILTERXML to return multiple rows and columns

[gdebouvertrinseocom]

hello Excel helpers,

I am trying to get FILTERXML return multiple rows and columns with one call. 

my xml example is :

<y><x><a>1.445</a><b>1.445</b></x><x><a>3.443</a><b>1.445</b></x><x><a>-1.443</a><b>10.445</b></x><x><a>3</a><b>1.445</b></x></y>

my xpath is :

//x/a | //x/b

Excel now returns 1 column with 8 rows, whilst I would like to get 2 columns, each having 4 rows ( 1 column for the a field, and one column for the b field )

Is this possible. Note that I cannot create two distinct FILTERXML functions as I need to have the resulting dynamic array covering the complete dataset.

thnaks for your advise

guido

[#2 · Accepted solution]

@gdebouvertrinseocom  there might be a more elegant solution, but this will work:

=LET(xml,"<y><x><a>1.445</a><b>1.445</b></x><x><a>3.443</a><b>1.445</b></x><x><a>-1.443</a><b>10.445</b></x><x><a>3</a><b>1.445</b></x></y>",x,"//x/a",y,"//x/b",CHOOSE({1,2},FILTERXML(xml,x),FILTERXML(xml,y)))

[#3]

unbelievable....
=CHOOSE({1,2},FILTERXML(xml,x),FILTERXML(xml,y))
does the job.

Thanks a lot - you saved my day - Excel is such as a great tool :)