I don't think there is a single formula that will do what you want but a pair of macro functions can do the job easily.

The first macro function (e.g., Quantity_Cost) is a subroutine that accepts a food name as a string and a quantity as an integer. It looks up the item price in for that name in Menu Costs, multiplies that value by the quantity, and returns the product to the calling macro.

The second macro function (e.g., Total_Cost) is the main routine. It extracts the text in the corresponding cell in the Food Option column. It determines if this text represents a single Food (as in rows 1, 2, and 3 of your sample) or it represents several Foods, each enclosed in quotes, and the entire list enclosed in brackets (as in row 5)

• For a single Food, call Quantity_Cost passing the text and the quantity (not shown in your sample). Return the value from this call to the cell from which Total_Cost was called (the Cost column in your sample).
• For multiple foods, you need a loop.
  • Find the first quote and note its location. If none, exit the loop.
    • Find the next quote and note its location. If none, the data is malformed.
      • Extract the text between, but not including, the quotes.
        • Call Quantity_Cost passing the extracted text and the quantity.
          • Add the value returned from this call to an accumulator.
            • Repeat the loop
• Return the value in the accumulator to the cell from which Total_Cost was called.

You can add additional error checking as you wish. For example: check for both brackets; check for a comma between quoted foods; verify the Food exists in Menu Costs; etc.

=SUMPRODUCT(VLOOKUP(T(IF({1},$B$2:$B$6)),$F$2:$G$9,2,)*$C$2:$C$6*($A$2:$A$6=J2))

Somebody gives the above solution, it's great.

The answer seems to tell that(I just presume, not sure):

1. IF({1},$B$2:$B$6) convert cells to static array.

2. the first argument lookup_value in VLOOKUP can be array.

Try using MATCH and CHOOSE:

=CHOOSE(MATCH(H2,{0,25,100,500,1000}),C2,D2,E2,F2,G2)*H2

The MATCH will compare the qty (in H2) to the values in the array ({0,25,100,500,1000}) and return a number (1 to 5).

The CHOOSE will then take this number and choose the nth value from the list of cells (C2,D2,E2,F2,G2)

We then multiply the value in the chosen cell by the quantity in H2

Hi Ben

I'm AnnaThomas and I'd be happy to help you with your question. In this Forum, we are Microsoft consumers just like yourself.

You can use the IF function in cell 3 to determine the price based on the selection in cell 1. For example:

=IF(A1=1,VLOOKUP(B1,Normal_Price_Range,2,FALSE),VLOOKUP(B1,Bundle_Price_Range,2,FALSE))

Where: A1 is the cell containing the drop-down menu selection (1 for normal price and 2 for bundle price) B1 is the cell containing the tariff selected from the drop-down menu in cell 2 Normal_Price_Range and Bundle_Price_Range are the named ranges for the normal and bundle prices respectively 2 is the column number in the named ranges that contains the prices FALSE means that the VLOOKUP function should perform an exact match

This formula will return the price from the Normal_Price_Range if the selection in cell 1 is 1, and from the Bundle_Price_Range if the selection is 2.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.