Function: FILTERXML

The FILTERXML function is a Web formula that can return specific data from an XML content, using a given XPath. This ability makes the function suitable for use in conjunction with the WEBSERVICE function. The FILTERXML function is available for 2013 and later versions of Excel on the Windows platform. In this guide, we’re going to show you how to use the FILTERXML function, and also go over some tips and error handling methods.

Supported versions

• Excel 2013 and later versions for Windows. Not available in Excel Online and Excel for Mac.

FILTERXML Function Syntax

Arguments

Examples

FILTERXML Function's Single Value Return

The FILTERXML function requires two arguments: The first is the XML text, and the second is the query language called XPath. The XPath stands for XML Path Language, and it uses a “path-like” syntax to identify the where the data is to be placed. “Path-like” here means a string that starts with double slashes, and contains a single slash for each folder (tag operator in XML).

Here is our sample data which contains information about stock quote for MSFT:

<QuoteApiModel>
    <Data>
        <Status>SUCCESS</Status>
        <Name>Microsoft Corp</Name>
        <Symbol>MSFT</Symbol>
        <LastPrice>137.08</LastPrice>
        <Change>-1.82</Change>
        <ChangePercent>-1.3102951764</ChangePercent>
        <Timestamp>Tue Jul 16 00:00:00 UTC-04:00 2019</Timestamp>
        <MarketCap>1050419091440</MarketCap>
        <Volume>22726128</Volume>
        <ChangeYTD>101.57</ChangeYTD>
        <ChangePercentYTD>34.9611105641</ChangePercentYTD>
        <High>139.05</High>
        <Low>136.52</Low>
        <Open>138.96</Open>
    </Data>
</QuoteApiModel>

If we need to grab the Name value from this XML, we need to use an XPath string like this:

Let's see how it works in Excel:

FILTERXML Function's Array Return

An alternative way to use the FILTERXML function is using it as an array formula to return all values with the same tags. This usage comes in handy if the return XML contains similar data, such as the dishes in a breakfast menu.

Here is another XML string that lists the stock values by a specified keyword. We used the “software” keyword in our example. Since the stock market doesn’t consist of a single company, we again get a long XML string with the same tags used more than once, signifying the same kind of data with different values.

<LookupResultList>
    <LookupResult>
        <Symbol>CHKP</Symbol>
        <Name>Check Point Software Technologies Ltd</Name>
        <Exchange>NASDAQ</Exchange>
    </LookupResult>
    <LookupResult>
        <Symbol>PRGS</Symbol>
        <Name>Progress Software Corp</Name>
        <Exchange>NASDAQ</Exchange>
    </LookupResult>
    <LookupResult>
        <Symbol>TTWO</Symbol>
        <Name>Take-Two Interactive Software Inc</Name>
        <Exchange>NASDAQ</Exchange>
    </LookupResult>
    <LookupResult>
        <Symbol>AMSWA</Symbol>
        <Name>American Software Inc</Name>
        <Exchange>NASDAQ</Exchange>
    </LookupResult>
    <LookupResult>
        <Symbol>GWRE</Symbol>
        <Name>Guidewire Software Inc</Name>
        <Exchange>NYSE</Exchange>
    </LookupResult>
    <LookupResult>
        <Symbol>DATA</Symbol>
        <Name>Tableau Software Inc</Name>
        <Exchange>NYSE</Exchange>
    </LookupResult>
    <LookupResult>
        <Symbol>PAYC</Symbol>
        <Name>Paycom Software Inc</Name>
        <Exchange>NYSE</Exchange>
    </LookupResult>
    <LookupResult>
        <Symbol>CYBR</Symbol>
        <Name>Cyberark Software Ltd</Name>
        <Exchange>NASDAQ</Exchange>
    </LookupResult>
    <LookupResult>
        <Symbol>COUP</Symbol>
        <Name>Coupa Software Inc</Name>
        <Exchange>NASDAQ</Exchange>
    </LookupResult>
    <LookupResult>
        <Symbol>PVTL</Symbol>
        <Name>Pivotal Software Inc</Name>
        <Exchange>NYSE</Exchange>
    </LookupResult>
</LookupResultList>

To get the symbol names we need to use a path like below.

If you try creating this path the same way as before, you will only get the first symbol of the stocks. The reason behind this is that the function can get all symbols as an array, but can only show the first one, if it is not set as an array formula. To convert a regular formula into an array equivalent can be done like this:

1. Begin by selecting a range that contains the same number of cells as the return values. For example, E3:E12
2. Type in your formula (i.e. =FILTERXML(E1,E2) ). Cell E2 contains the same XPath string: //LookupResultList/LookupResult/Symbol
3. Press the Ctrl + Shift + Enter key combination instead of Enter. You do not need this step if you have the current version of Excel 365. Enter will be enough to see all values. For more information, please visit our article about Excel's new concept: Dynamic Arrays

All that's left to do is getting all the symbols together.

Download Workbook

Tips

• Use the FILTERXML to parse the XML returns from the WEBSERVICE function.
• The FILTERXML function may appear in the Excel for Mac function gallery, but it doesn't work on Mac, because of the function's dependencies on Windows operating system features.