Separating CSV data into rows

Hi rshenk3 

If I understand your needs correctly, and you have an Excel version with FILTERXML and XLOOKUP you can do it without macros.

The main problem seems to be separating the csv data, and that can be done using FILTERXML.

=FILTERXML("<c><r>"&SUBSTITUTE(H7;",";"</r><r>")&"</r></c>";"//r")

Substitute the commas to make it parsable as XML;

<c><r>1</r><r>2</r><r>3</r><r>4</r></c>

That formula in H10 results in one column with multiple rows.

J10 shows the SUMPRODUCT, or simply =SUM(H10#*I10#) assuming Excel 365.

In the attaced file, you also have an example of looking up the wanted invoice row. cell P10

=LET(
   qtyCell;     XLOOKUP(H3;B:B;H:H);
   priceCell;   XLOOKUP(H3;B:B;I:I);
   qty;         FILTERXML("<c><r>"&SUBSTITUTE(qtyCell;",";"</r><r>")&"</r></c>";"//r");
   price;       FILTERXML("<c><r>"&SUBSTITUTE(priceCell;",";"</r><r>")&"</r></c>";"//r");
   output;      SUM(qty*price);
   output
)