lookup to array in one cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2686161%22%20slang%3D%22en-US%22%3Elookup%20to%20array%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2686161%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3Bhi%2C%3C%2FP%3E%3CP%3Ei%20have%20a%20array%20in%20%3CSTRONG%3Eone%20cell%3C%2FSTRONG%3E%2C%20like%20this%3A%3C%2FP%3E%3CP%3Edog%2Ccat%2Cfish%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20i%20have%20a%20%3CSTRONG%3Etable%3C%2FSTRONG%3E%20of%20prices%2C%20like%20this%3A%3C%2FP%3E%3CP%3Edog%20-%201%3C%2FP%3E%3CP%3Ecat%20-%202%3C%2FP%3E%3CP%3Efish%20-3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20want%20to%20get%20a%20formula%20that%20sum%20all%20the%20array%2C%201%2B2%2B3%3D6.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20can%20split%20the%20array%20to%20many%20cells%2C%20and%20do%20lookup%20and%20sum%2C%20but%20i%20have%20long%20array...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehelp!!%20tY!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2686161%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-2686261%22%20slang%3D%22en-US%22%3ERe%3A%20lookup%20to%20array%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2686261%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1137802%22%20target%3D%22_blank%22%3E%40Eladnoam2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWho%20ever%20produced%20this%20data%20should%20be%20hit%20with%20the%20workbook.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DSUM(XLOOKUP(FILTERXML(%22%3CY%3E%3CZ%3E%22%26amp%3BSUBSTITUTE(A1%2C%22%2C%22%2C%22%3C%2FZ%3E%3CZ%3E%22)%26amp%3B%22%3C%2FZ%3E%3C%2FY%3E%22%2C%22%2F%2Fz%22)%2C%24E%241%3A%24E%243%2C%24F%241%3A%24F%243))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2686895%22%20slang%3D%22en-US%22%3ERe%3A%20lookup%20to%20array%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2686895%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ety!!%20king!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2687099%22%20slang%3D%22en-US%22%3ERe%3A%20lookup%20to%20array%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2687099%22%20slang%3D%22en-US%22%3Ein%20your%20data%20the%20array%20write%20in%20the%20next%20lows%20(by%20%3CZ%3E)%2C%3CBR%20%2F%3Ehow%20can%20i%20write%20it%20in%20the%20next%20column%3F%20(by%20)%3F%20ty%3CBR%20%2F%3E%5Bi%20ask%20because%20i%20have%20a%20table%2C%20and%20your%20data%20dosen%20work...%5D%3C%2FZ%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2687235%22%20slang%3D%22en-US%22%3ERe%3A%20lookup%20to%20array%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2687235%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1137802%22%20target%3D%22_blank%22%3E%40Eladnoam2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20not%20my%20data%2C%20it's%20your%20data.%3C%2FP%3E%3CP%3EAnd%20if%20you%20think%20the%20formula%20does%20not%20work%20then%20please%20provide%20a%20sample%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2696037%22%20slang%3D%22en-US%22%3ERe%3A%20lookup%20to%20array%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2696037%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3Bhi%2C%3C%2FP%3E%3CP%3Eits%20work%26nbsp%3Bexcellent%20with%20Excel.%20but%20i%20need%20use%20it%20with%20Excel%20online%2C%20and%20the%20function%20'FILTERXML'%20dosent%20work%20there.%20can%20u%20help%20my%20again%3F%20sorry....%20ty%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

 hi,

i have a array in one cell, like this:

dog,cat,fish

 

and i have a table of prices, like this:

dog - 1

cat - 2

fish -3

 

i want to get a formula that sum all the array, 1+2+3=6.

 

i can split the array to many cells, and do lookup and sum, but i have long array...

 

help!! tY!

7 Replies

@Eladnoam2 

Who ever produced this data should be hit with the workbook.

=SUM(XLOOKUP(FILTERXML("<y><z>"&SUBSTITUTE(A1,",","</z><z>")&"</z></y>","//z"),$E$1:$E$3,$F$1:$F$3))

@Detlef Lewin 

ty!! king!!

in your data the array write in the next lows (by <z>),
how can i write it in the next column? (by <?>)? ty
[i ask because i have a table, and your data dosen work...]

@Eladnoam2 

It's not my data, it's your data.

And if you think the formula does not work then please provide a sample workbook.

 

@Detlef Lewin hi,

its work excellent with Excel. but i need use it with Excel online, and the function 'FILTERXML' dosent work there. can u help my again? sorry.... ty

 

@Eladnoam2 

As variant

image.png

However not very reliable (fish will be found in fishmeal)

ty. u help my so much!!