Creating Total Cost for a List of Items in Square Brackets Separated by Commas

Question

I have a list of items with their corresponding prices stored in a separate sheet named 'Menu Costs' in Excel. I want to create a formula in another sheet that calculates the total cost for a list of items I enter into a cell. Each item will be enclosed within square brackets, and I will separate the items with commas if there is more than one item which is due to me using a power automate flow to input the data automatically from forms into the excel sheet

My Objective:

1. Extract the individual items from “food options” cell looks like: like this ["Hot/Cold Drinks & biscuit","Tray bake/cookie”].
2. Look up the prices for each item in the 'Menu Costs' sheet.
3. Multiply each item's price by a specified quantity.
4. Sum up the individual costs to calculate the total cost.

Excel sheet:

Menu Costs:

Current formula in cost column which works if I use one Food field within the Food Options column

=VLOOKUP(E2,'Menu Costs'!A:B,2,FALSE)*C2

PLease help!

Answer 1

To calculate the total cost for a list of items entered in a cell, you can use the following approach in Excel:

Split Items: Use a formula to split the items into an array.

Lookup Prices: Use VLOOKUP or INDEX/MATCH to find prices from the "Menu Costs" sheet.

Sum Prices: Sum the prices to get the total cost.

For example, use TEXTSPLIT (or a custom function for older Excel versions) to split the items, and SUMPRODUCT with VLOOKUP to calculate the total.

Regarding Jack in the Box menu with prices, the menu offers a variety of fast food options including burgers, tacos, and breakfast items, making it a convenient choice for diverse tastes.