Google Sheet: formula to loop through a range

Question

It's not hard to do this with custom function, but I'm wondering if there is a way to do it using a formula. Because datas won't automatically update when using custom function.

So I have a course list sheet, each with a price. And I'm using google form to let users choose what courses they will take. Users are allowed to take multiple courses, so how many they will take is unknown. Now in the response sheet, I have datas like

Order ID	User ID	Courses	Total
1001	38	courseA, courseC	What formula to put here?
1002	44	courseB, courseC, courseD	What formula to put here?
1003	55	courseE	What formula to put here?

and the course sheet is like

course	Price
A	23
B	33
C	44
D	23
E	55

I want to output the total for each order and am looking at using FILTER to do this. Firstly I can get a range of unknown length for the chosen courses

=SPLIT(courses, ",") // having named the Courses column as "courses"

Now I need to filter this range against the course sheet? not quite sure how to do it or even if it is possible. Any hint is appreicated.

Answer 1

try:

=ARRAYFORMULA(IF(A2:A="",,MMULT(IFERROR(
 VLOOKUP(SPLIT(C2:C, ", "), {F1&F2:F, G2:G}, 2, 0))*1, 
 ROW(INDIRECT("1:"&COLUMNS(SPLIT(C2:C, ", "))))^0)))

demo spreadsheet

Answer 2

As I need time to digest @player0's answer, I am doing this in a more intuitive way. I create 2 sheets to store intermediate values. The first one is named "chosen_courses"

Order ID	User ID
1001	=IFERROR(ARRAYFORMULA(TRIM(SPLIT(index(courses,Row(),1),","))),"")
1002	=IFERROR(ARRAYFORMULA(TRIM(SPLIT(index(courses,Row(),1),","))),"")
1003	=IFERROR(ARRAYFORMULA(TRIM(SPLIT(index(courses,Row(),1),","))),"")

In this sheet every row is a horizontal list of the chosen courses, and I created another sheet

total	course price
=IF(isblank(order_id),"",SUM(B2:2))	=IFERROR(VLOOKUP('chosen_courses'!B2,{course_Names,course_price},2,false),"")
=IF(isblank(order_id),"",SUM(C2:2))	=IFERROR(VLOOKUP('chosen_courses'!B2,{course_Names,course_price},2,false),"")
=IF(isblank(order_id),"",SUM(D2:2))	=IFERROR(VLOOKUP('chosen_courses'!B2,{course_Names,course_price},2,false),"")

course_Names,order_id and course_price are named ranges.

This works well, at least for now.

But there is a problem: I have 20 courses, so in the 2nd sheed, there are 21 columns. And I copy the formulas to 1000 rows because that is the maximum rows you can get to using ctrl+shift+↓ and ctrl+D. Now sometimes when I open the sheet, there will be a progress bar calculating formulas in this sheet, which could take around 2 mins, even though I have only like 5 testing orders in the sheet. I am afraid this will get worse when I have more datas or when it is open by old computers. Is it because I use some resource consuming functions? Can it be improved?