FILTERXML function

FILTERXML parses a text string containing XML and returns the values that match a supplied XPath query, spilling results into adjacent cells.

=FILTERXML(xml, xpath)

Generate a FILTERXML formula

Describe what you need. The generator will reach for FILTERXML where FILTERXML is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How FILTERXML reads its arguments
xmlrequiredxpathrequiredFILTERXML →
ArgumentRequiredDescription
xmlRequiredA text string containing well-formed XML; if the XML is malformed FILTERXML returns #VALUE!.
xpathRequiredA text string with a valid XPath query; if the query finds no matches FILTERXML returns #N/A.

Returns

It returns a string or an array of strings that match the XPath expression.

Availability

Excel: Windows only · Google Sheets: Not available

Worked examples

1. List order IDs for the East region

1001
1003
=FILTERXML("<root><row><orderid>1001</orderid><region>East</region><rep>Alice</rep><units>5</units><unitprice>20</unitprice><orderdate>2023-01-15</orderdate></row><row><orderid>1002</orderid><region>West</region><rep>Bob</rep><units>3</units><unitprice>15</unitprice><orderdate>2023-01-20</orderdate></row><row><orderid>1003</orderid><region>East</region><rep>Carol</rep><units>7</units><unitprice>22</unitprice><orderdate>2023-02-05</orderdate></row><row><orderid>1004</orderid><region>South</region><rep>Dave</rep><units>2</units><unitprice>18</unitprice><orderdate>2023-02-12</orderdate></row><row><orderid>1005</orderid><region>West</region><rep>Eve</rep><units>6</units><unitprice>19</unitprice><orderdate>2023-03-01</orderdate></row></root>", "//row[region='East']/orderid")

Result: A vertical spill array containing 1001 and 1003.

The XML string encodes five sales rows. The XPath //row[region='East']/orderid selects the <orderid> element of every <row> whose <region> child equals "East". Only rows 1001 and 1003 satisfy that condition, so FILTERXML returns those two IDs in separate rows.

2. Retrieve the unit price for order 1002

15
=FILTERXML("<root><row><orderid>1001</orderid><region>East</region><rep>Alice</rep><units>5</units><unitprice>20</unitprice><orderdate>2023-01-15</orderdate></row><row><orderid>1002</orderid><region>West</region><rep>Bob</rep><units>3</units><unitprice>15</unitprice><orderdate>2023-01-20</orderdate></row><row><orderid>1003</orderid><region>East</region><rep>Carol</rep><units>7</units><unitprice>22</unitprice><orderdate>2023-02-05</orderdate></row><row><orderid>1004</orderid><region>South</region><rep>Dave</rep><units>2</units><unitprice>18</unitprice><orderdate>2023-02-12</orderdate></row><row><orderid>1005</orderid><region>West</region><rep>Eve</rep><units>6</units><unitprice>19</unitprice><orderdate>2023-03-01</orderdate></row></root>", "//row[orderid='1002']/unitprice")

Result: The single string "15".

The XPath expression isolates the <unitprice> node belonging to the row whose <orderid> equals "1002". Because that row exists and has a unit price of 15, FILTERXML returns the value as a one-cell result.

3. Show order dates for orders with more than four units

2023-01-15
2023-02-05
2023-03-01
=FILTERXML("<root><row><orderid>1001</orderid><region>East</region><rep>Alice</rep><units>5</units><unitprice>20</unitprice><orderdate>2023-01-15</orderdate></row><row><orderid>1002</orderid><region>West</region><rep>Bob</rep><units>3</units><unitprice>15</unitprice><orderdate>2023-01-20</orderdate></row><row><orderid>1003</orderid><region>East</region><rep>Carol</rep><units>7</units><unitprice>22</unitprice><orderdate>2023-02-05</orderdate></row><row><orderid>1004</orderid><region>South</region><rep>Dave</rep><units>2</units><unitprice>18</unitprice><orderdate>2023-02-12</orderdate></row><row><orderid>1005</orderid><region>West</region><rep>Eve</rep><units>6</units><unitprice>19</unitprice><orderdate>2023-03-01</orderdate></row></root>", "//row[number(units)>4]/orderdate")

Result: A vertical spill array of the three dates that meet the units>4 condition.

The XPath uses number(units)>4 to compare the numeric value of each <units> element. Rows 1001, 1003 and 1005 have 5, 7 and 6 units respectively, so their <orderdate> values are returned in order.

Common errors

Which FILTERXML error are you seeing?
FILTERXML returned an error#VALUE!
Correct the XML so every opening tag has a matching closing tag and the document is properly nested.
#N/A
Adjust the XPath to reference values that are present, or wrap the call in IFERROR to supply an alternate result.
#SPILL!
Clear the target range or move the formula to a location with enough empty cells to accommodate the spill.
ErrorWhy it happensHow to fix it
#VALUE!The XML string is not well-formed (e.g., a missing closing tag).Correct the XML so every opening tag has a matching closing tag and the document is properly nested.
#N/AThe XPath query matches no nodes, such as looking for a rep that does not exist.Adjust the XPath to reference values that are present, or wrap the call in IFERROR to supply an alternate result.
#SPILL!The result would spill into cells that already contain data.Clear the target range or move the formula to a location with enough empty cells to accommodate the spill.

Tips and when to use something else

  • Build the XML string dynamically with TEXTJOIN or CONCAT to keep it in sync with your source table.
  • Use WEBSERVICE to pull live XML data from a web API, then pipe it directly into FILTERXML.
  • If you only need to look up values in a regular table, XLOOKUP or INDEX/MATCH is faster and avoids XML parsing.
  • Remember that FILTERXML is Windows-only; on macOS or Google Sheets you must use alternative approaches.

Related functions

WEBSERVICEWEBSERVICE pulls the raw response from a given URL and returns it as a plain‑text string, letting you import live web data into Excel worksheets.ENCODEURLENCODEURL converts a text string into a percent-encoded URL component, escaping characters that are illegal in web addresses.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.

Frequently asked questions

Can FILTERXML parse JSON data?
No. FILTERXML works exclusively with XML. To extract data from JSON you would first need to convert it to XML or use Power Query, which has native JSON support.
Why does FILTERXML return #VALUE! even though my XML looks correct?
Excel is very strict about XML well-formedness. Even a stray ampersand (&) or an unescaped quote inside a text node will cause #VALUE!. Validate the XML with an external parser or escape special characters.
How can I retrieve multiple fields (e.g., order ID and unit price) in one FILTERXML call?
FILTERXML returns a single node set per call. To get multiple columns you need separate formulas for each XPath, or construct an XPath that returns a concatenated string and then split it with TEXTSPLIT.
Is FILTERXML available in Excel for the web or on Mac?
FILTERXML is only implemented in the Windows desktop version of Excel. Mac users can achieve similar results with the GETPIVOTDATA function for structured data or by using Power Query to import XML.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator