Text Extraction and Sum with Vlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-3281357%22%20slang%3D%22en-US%22%3EText%20Extraction%20and%20Sum%20with%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3281357%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20thought%20all%20day%20about%20this%20problem%20without%20any%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20with%20a%20many%20codes%20in%20single%20cell%20(see%20below).%20I%20need%20to%20extract%20(%20comma%20as%20delimiter)%20each%20single%20code%20and%20use%20a%20vlookup%20in%20other%20table%20to%20sum%20all%20codes%20present%20in%20that%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20the%20best%20option%20is%20to%20use%20a%20matrix%20formula%20but%20I%20have%20no%20idea%20how%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22128%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22334.906px%22%20height%3D%2242px%22%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23000000%22%3EES1094%2CES1093%2CES1097%2CES1096%2CES1092%2CES1095%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2242px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22334.906px%22%20height%3D%2214px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2214px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22334.906px%22%20height%3D%2214px%22%3EES1094%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2214px%22%3E5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22334.906px%22%20height%3D%2214px%22%3EES1093%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2214px%22%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22334.906px%22%20height%3D%2214px%22%3EES1097%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2214px%22%3E5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22334.906px%22%20height%3D%2214px%22%3EEs1096%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2214px%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22334.906px%22%20height%3D%2214px%22%3EES1092%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2214px%22%3E6%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22334.906px%22%20height%3D%2214px%22%3EES1095%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2214px%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22334.906px%22%20height%3D%2214px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2214px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22334.906px%22%20height%3D%2214px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2214px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22334.906px%22%20height%3D%2214px%22%3EResults%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2214px%22%3E29%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGV%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3281357%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-3282082%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20Extraction%20and%20Sum%20with%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3282082%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1359159%22%20target%3D%22_blank%22%3E%40GreVia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20in%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Blast%20reply%2C%20a%20dynamic%20array%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F362993i5263F7D19927257E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22_Screenshot.png%22%20alt%3D%22_Screenshot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ein%20%3CSTRONG%3EC2%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(%0A%20%20%20%20SplitStr%2C%20FILTERXML(%22%3CT%3E%3CW%3E%22%20%26amp%3B%20SUBSTITUTE(A1%2C%22%2C%22%2C%22%3C%2FW%3E%3CW%3E%22)%20%26amp%3B%20%22%3C%2FW%3E%3C%2FT%3E%22%2C%20%22%2F%2Fw%22)%2C%0A%20%20%20%20Lookups%2C%20%20XLOOKUP(SplitStr%2C%20F2%3AF7%2C%20G2%3AG7%2C0)%2C%0A%20%20%20%20Rows%2C%20%20%20%20%20ROWS(SplitStr)%2C%0A%20%20%20%20SeqC%2C%20%20%20%20%20SEQUENCE(%2C2)%2C%0A%20%20%20%20IF(SEQUENCE(Rows%20%2B1)%20%26lt%3B%3D%20Rows%2C%0A%20%20%20%20%20%20%20CHOOSE(SeqC%2C%20SplitStr%2C%20Lookups)%2C%0A%20%20%20%20%20%20%20CHOOSE(SeqC%2C%20%22Total%22%2C%20SUM(Lookups))%0A%20%20%20%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThe%20%3CEM%3E%3CSTRONG%3ELookupRange%3C%2FSTRONG%3E%3C%2FEM%3E%20can%20be%20on%20a%20different%20sheet.%20Corresponding%20sample%20attache%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3282033%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20Extraction%20and%20Sum%20with%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3282033%22%20slang%3D%22en-US%22%3EI%20have%20Office365.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3282026%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20Extraction%20and%20Sum%20with%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3282026%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1359159%22%20target%3D%22_blank%22%3E%40GreVia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20all%20depends%20on%20which%20Excel%20version%2Fplatform%20do%20you%20use%20and%20how%20your%20data%20is%20structured.%20In%20any%20case%20it'll%20be%20few%20formulas%20-%20split%20codes%2C%20lookup%20values%20for%20codes%20and%20sum%20them.%20On%20Excel%20with%20dynamic%20arrays%20perhaps%20you%20may%20combine%20all%20together%20in%20one%20block.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3281828%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20Extraction%20and%20Sum%20with%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3281828%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThanks%20you%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20there%20a%20possibility%20to%20do%20also%20the%20sum%20through%20Vlookup%20in%20a%20solo%20formula%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3281819%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20Extraction%20and%20Sum%20with%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3281819%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1359159%22%20target%3D%22_blank%22%3E%40GreVia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20split%20codes%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20506px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F362932i6B7BA39BD7C23C69%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DFILTERXML(%22%3CT%3E%3CS%3E%22%26amp%3BSUBSTITUTE(C3%2C%22%2C%22%2C%22%3C%2FS%3E%3CS%3E%22)%26amp%3B%22%3C%2FS%3E%3C%2FT%3E%22%2C%20%22%2F%2Fs%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New 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