Forum Discussion

GreVia's avatar
GreVia
Copper Contributor
Apr 10, 2022

Text Extraction and Sum with Vlookup

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 cod...
excel
Formulas and Functions
GreVia's avatar
GreVia
Copper Contributor
Apr 11, 2022

SergeiBaklan Thanks you,

 

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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 11, 2022

    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.

    • GreVia's avatar
      GreVia
      Copper Contributor
      Apr 11, 2022
      I have Office365.
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Apr 11, 2022

        Hi GreVia 

         

        As in SergeiBaklan last reply, a dynamic array:

        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

Resources