Apr 10 2022 12:38 PM
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 | |
ES1094 | 5 |
ES1093 | 3 |
ES1097 | 5 |
Es1096 | 8 |
ES1092 | 6 |
ES1095 | 2 |
Results | 29 |
Many thanks
GV
Apr 11 2022 04:44 AM
You may split codes
with
=FILTERXML("<t><s>"&SUBSTITUTE(C3,",","</s><s>")&"</s></t>", "//s")
Apr 11 2022 04:48 AM
@Sergei Baklan Thanks you,
is there a possibility to do also the sum through Vlookup in a solo formula?
Apr 11 2022 07:49 AM
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.
Apr 11 2022 08:40 AM
Hi @GreVia
As in @Sergei Baklan 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