Text Extraction and Sum with Vlookup

Question

Hello all,&nbsp;I have thought all day about this problem without any solution.&nbsp;I have a table with a many codes in single cell (see below). I need to extract ( comma as delimiter) each single code and use a vlookup in other table to sum all codes present in that cell.&nbsp;Maybe the best option is to use a matrix formula but I have no idea how to do.&nbsp;&nbsp;ES1094,ES1093,ES1097,ES1096,ES1092,ES1095&nbsp;&nbsp;&nbsp;ES10945ES10933ES10975Es10968ES10926ES10952&nbsp;&nbsp;&nbsp;&nbsp;Results29&nbsp;&nbsp;Many thanks&nbsp;GV

sergeibaklan · Answer

GreVia&nbsp;
You may split codes

with
=FILTERXML("&lt;t&gt;&lt;s&gt;"&amp;SUBSTITUTE(C3,",","&lt;/s&gt;&lt;s&gt;")&amp;"&lt;/s&gt;&lt;/t&gt;", "//s")

grevia · Answer

SergeiBaklan&nbsp;Thanks you,&nbsp;is there a possibility to do also the sum through Vlookup in a solo formula?

sergeibaklan · Answer

GreVia&nbsp;
It all depends on which Excel version/platform do you use and how your data is structured. In any case it'll be few formulas - split codes, lookup values for codes and sum them. On Excel with dynamic arrays perhaps you may combine all together in one block.

grevia · Answer

I have Office365.

lorenzo · Answer

Hi&nbsp;GreVia&nbsp;&nbsp;As in&nbsp;SergeiBaklan&nbsp;last reply, a dynamic array:in C2:=LET(
    SplitStr, FILTERXML("&lt;t&gt;&lt;w&gt;" &amp; SUBSTITUTE(A1,",","&lt;/w&gt;&lt;w&gt;") &amp; "&lt;/w&gt;&lt;/t&gt;", "//w"),
    Lookups,  XLOOKUP(SplitStr, F2:F7, G2:G7,0),
    Rows,     ROWS(SplitStr),
    SeqC,     SEQUENCE(,2),
    IF(SEQUENCE(Rows +1) &lt;= Rows,
       CHOOSE(SeqC, SplitStr, Lookups),
       CHOOSE(SeqC, "Total", SUM(Lookups))
    )
)The LookupRange can be on a different sheet. Corresponding sample attache

Forum Discussion

Text Extraction and Sum with Vlookup

5 Replies

Resources

ES1094,ES1093,ES1097,ES1096,ES1092,ES1095

ES1094	5
ES1093	3
ES1097	5
Es1096	8
ES1092	6
ES1095	2


Results	29