Error using Filterxml and Webservice functions

%3CLINGO-SUB%20id%3D%22lingo-sub-2296127%22%20slang%3D%22en-US%22%3EError%20using%20Filterxml%20and%20Webservice%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2296127%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys%2C%3C%2FP%3E%3CP%3EGood%20day.%3C%2FP%3E%3CP%3EI%20need%20help%20regarding%20the%20use%20of%20Filterxml%20and%20Webservice%20functions%20because%20I%20followed%20the%20right%20syntax%20for%20the%20formula%20but%20it%20doesn't%20give%20the%20right%20output.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFilterxml%3C%2FP%3E%3CP%3E1.%20Cell%20A1%20entered%20%3A%202%2C5%2C8%2C9%3C%2FP%3E%3CP%3E2.%20In%20Cell%20B2%20converted%20cell%20A1%20to%20XML%20using%20this%20formula%20%3A%20%3D%20%22%3CSTART%3E%3CNUM%3E%22%26amp%3Bsubstitute(A1%2C%22%2C%22%2C%22%3C%2FNUM%3E%3CNUM%3E%22)%26amp%3B%22%3C%2FNUM%3E%3C%2FSTART%3E%22%3C%2FP%3E%3CP%3E3.%20In%20Cell%20C3%20%3D%20Filterxml(B2%2C%22%2F%2FNum%22)%3C%2FP%3E%3CP%3E4.%20Answer%20%3A%202%26nbsp%3B%20%26nbsp%3Bonly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWebservice%3C%2FP%3E%3CP%3E1.%20In%20Cell%20D1%20copy%20and%20paste%20a%20url%20from%20a%20website%3C%2FP%3E%3CP%3E2.%20In%20E1%20entered%20this%20%3A%20%3Dwebservice(D1)%26nbsp%3B%20%26nbsp%3Band%20the%20output%20is%20Value%20error%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2296127%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2297068%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20using%20Filterxml%20and%20Webservice%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2297068%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037617%22%20target%3D%22_blank%22%3E%40ermendiola0901%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFILTERXML%20returns%20an%20array.%20If%20you%20are%20on%20Excel%20which%20doesn't%20support%20dynamic%20arrays%20you%20shall%20use%20Ctrl%2BShift%2BEnter%2C%20otherwise%20it%20returns%20only%20first%20element%20of%20the%20array%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20850px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275772i5EA31CEBF5F2D466%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2297070%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20using%20Filterxml%20and%20Webservice%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2297070%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037617%22%20target%3D%22_blank%22%3E%40ermendiola0901%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWEBSERVICE()%20-%20depends%20on%20URL.%20Please%20check%20here%20why%20%23VALUE%20could%20be%20returned%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-ie%2Foffice%2Fwebservice-function-0546a35a-ecc6-4739-aed7-c0b7ce1562c4%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EWEBSERVICE%20function%20-%20Excel%20(microsoft.com)%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EHow%20to%20use%20-%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-webservice-function%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EHow%20to%20use%20the%20Excel%20WEBSERVICE%20function%20%7C%20Exceljet%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2301873%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20using%20Filterxml%20and%20Webservice%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2301873%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3EThanks%20for%20your%20help.%20I'm%20currently%20using%20office%20365%20and%20office%202019%20both%20of%20them%20installed%20at%20the%20same%20time%20when%20I%20renewed%20my%20subscription.%20Do%20you%20have%20any%20idea%20why%20am%20I%20not%20get%20dyanamic%20array%20support%20because%20I%20already%20tried%20control%20shift%20enter%20and%20it%20returns%20one%20value%20only.%3C%2FLINGO-BODY%3E
New Contributor

Hi Guys,

Good day.

I need help regarding the use of Filterxml and Webservice functions because I followed the right syntax for the formula but it doesn't give the right output.

 

Filterxml

1. Cell A1 entered : 2,5,8,9

2. In Cell B2 converted cell A1 to XML using this formula : = "<Start><Num>"&substitute(A1,",","</Num><Num>")&"</Num></Start>"

3. In Cell C3 = Filterxml(B2,"//Num")

4. Answer : 2   only.

 

Webservice

1. In Cell D1 copy and paste a url from a website

2. In E1 entered this : =webservice(D1)   and the output is Value error

 

Please help.

 

6 Replies

@ermendiola0901 

FILTERXML returns an array. If you are on Excel which doesn't support dynamic arrays you shall use Ctrl+Shift+Enter, otherwise it returns only first element of the array

image.png

 

@ermendiola0901 

WEBSERVICE() - depends on URL. Please check here why #VALUE could be returned WEBSERVICE function - Excel (microsoft.com)

How to use - How to use the Excel WEBSERVICE function | Exceljet

 

Hi,
Thanks for your help. I'm currently using office 365 and office 2019 both of them installed at the same time when I renewed my subscription. Do you have any idea why am I not get dyanamic array support because I already tried control shift enter and it returns one value only.

@ermendiola0901 

I'm not sure you may install desktop version of 365 and Excel 2019 in parallel on one machine. Perhaps you work with 2019 which doesn't support dynamic arrays.

Hi@sergei baklan,
Actually I tried to uninstall office 365 and install it again for 5 times. It seems that office 365 is a web version and when you click at the upper tool bar "use desktop" it will launch office 2019 desktop version. In recognize webservice and filterxml in office 2019 but the problem is it has an error value.

@ermendiola0901 

Not sure how did you uninstall, in this case clean uninstallation is required instructions are here https://support.microsoft.com/en-us/office/uninstall-office-from-a-pc-9dd49b83-264a-477a-8fcc-2fdf5d...

Office 365 could be online only, could be with desktop applications, it depends on your subscription. If Excel Desktop is included in your subscription, you need to make clean uninstall of 2019 (and 365 if you tried to install it), and after that to install Office 365 desktop applications from scratch.