SOLVED

lookup to array in one cell

Copper 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

 

best response confirmed by allyreckerman (Microsoft)
Solution

@Eladnoam2 

As variant

image.png

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

ty. u help my so much!!
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Eladnoam2 

As variant

image.png

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

View solution in original post