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.

Your system of equations is meaningless, because you cannot add apples with oranges, much less get dollars as the sum. Let's rewrite your system as $$\begin{align} (4\mathrm{~apples})\cdot x+(16\mathrm{~oranges})\cdot y & = 25\mathrm{~dollars}\\ (8\mathrm{~apples})\cdot x+(48\mathrm{~oranges})\cdot y & = 96\mathrm{~dollars}\end{align}$$ where $\cdot$ means multiplication, $x$ is the unit price of apples (in dollars per apple), and $y$ is the unit price of oranges (in dollars per orange).

Subtract twice the first equation from the second and you will get $y=2.875\mathrm{~dollars/orange}$. Then plugging in this value to one of the original equations yields $x=-5.25\mathrm{~dollars/apple}$.

Now, this means that you pay the supplier 2.875 dollars for each orange you buy, and the supplier pays you 5.25 dollars for each apple you buy. This doesn't make much business sense, so most likely the supplier is in error.

Following Aretino's comment, you want to find the price for a regular pizza and the price for a small pizza. Since we don't know these, let's label them $x$ and $y$.

$x = $regular pizza price

$y =$small pizza price

From here, we make two equations with the information we are given. $2$ regular pizzas and $3$ small pizzas cost £$28$, so $2x + 3y = 28$. Also, $8$ regular pizzas and $9$ small pizzas cost £$100$, so $8x + 9y = 100.$

This gives us a system of equations. $$2x + 3y = 28\\ 8x + 9y = 100$$

Solving this system of equations using any method you prefer will give you the price for each pizza.

If you want to learn and not only searching for someone to solve your homework you should look into what variables are and how to use them.

Either way, here is a possible solution:

workbooks = int(input('Enter number of workbooks: ')) * 8.5 #number from input times price
textbooks = int(input('Enter number of textbooks: ')) * 24
magazines = int(input('Enter number of magazines: ')) * 5.95

cost = workbooks + textbooks + magazines #combined price of all three types
tax = cost * 0.06 # 6% of combined price -> tax
total = cost + tax # sum of combined price and tax

# combining all calculated values in an f-string using ':.2f' for 2 fixed digits after decimal 
print(f'Cost before tax: ${cost:.2f} Sales tax: ${tax:.2f} Cost after tax: ${total:.2f}')