Text Extraction and Sum with Vlookup

Copper Contributor

Hello all,

 

I have thought all day about this problem without any solution.

 

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.

 

Maybe the best option is to use a matrix formula but I have no idea how to do.

 

 

ES1094,ES1093,ES1097,ES1096,ES1092,ES1095 
  
ES10945
ES10933
ES10975
Es10968
ES10926
ES10952
  
  
Results29

 

 

Many thanks

 

GV

5 Replies

@GreVia 

You may split codes

image.png

with

=FILTERXML("<t><s>"&SUBSTITUTE(C3,",","</s><s>")&"</s></t>", "//s")

@Sergei Baklan Thanks you,

 

is there a possibility to do also the sum through Vlookup in a solo formula?

@GreVia 

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.

I have Office365.

Hi @GreVia 

 

As in @Sergei Baklan last reply, a dynamic array:

_Screenshot.png

in C2:

=LET(
    SplitStr, FILTERXML("<t><w>" & SUBSTITUTE(A1,",","</w><w>") & "</w></t>", "//w"),
    Lookups,  XLOOKUP(SplitStr, F2:F7, G2:G7,0),
    Rows,     ROWS(SplitStr),
    SeqC,     SEQUENCE(,2),
    IF(SEQUENCE(Rows +1) <= Rows,
       CHOOSE(SeqC, SplitStr, Lookups),
       CHOOSE(SeqC, "Total", SUM(Lookups))
    )
)

The LookupRange can be on a different sheet. Corresponding sample attache