The FILTERXML function returns specific data from XML content by using the specified xpath.
The FILTERXML function is not available in Excel for the web and Excel for Mac.
This function may appear in the function gallery in Excel for Mac, but it relies on features of the Windows operating system, so it will not return results on Mac.
The FILTERXML function syntax has the following arguments.
xml Required. A string in valid XML format.
xpath Required. A string in standard XPath format.
If xml is not valid, FILTERXML returns the #VALUE! error value.
If xml contains a namespace with a prefix that is not valid, FILTERXML returns the #VALUE! error value.
This example uses the FILTERXML function on XML data returned in cell B2, which is provided by the results of the WEBSERVICE function on the web query in cell B1.
The data from FILTERXML is returned in cells B3:B5 and C3:C5, and shows the three most recently updated query results on Wikipedia and the time of their updates (in UTC, "Coordinated Universal Time").
Cells B3:B5 contain the formula =FILTERXML(B3,"//rc/@title"), which needs to be entered as an array formula in those cells. Select B3:C5, enter the formula, and press Ctrl+Shift+Enter. Cells C3:C5 contain the formula =FILTERXML(B3,"//rc/@timestamp"), and also needs to be entered as an array formula.
Note: If you have a current version of Office 365, then you can simply enter the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by first selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you. For more information on array formulas, see Guidelines and examples of array formulas.