I was wondering if anyone could help me with creating an array formula in Excel. I am trying to do a lookup on a value and sum the costs all in one cell. Attached is a workbook that has a simple example of the idea I'm trying to accomplish. The total cell should lookup the price for each food item and multiply it by the amount. It should then sum those values and tell me the total price.
For some reason, the lookup formula isn't working. It looks up the value for price of the first item in the list and multiplies the quantities by that price alone. It should look up an item's price and multiply its quantity by that price then lookup the next price and do the same for that fruit's quantity. I'm not sure why it's not working. Can you do lookups in CSE formulas?
Thank you for any help you can give.