Which is the best function

I am using Excel 2016. I need to figure how to get the cell to calculate the total by adding specific amounts which are based on an index. 

For the example below,

The assumptions are customers will only buy up to 3 items. and will only get 1 of each item

The order of the item matters.

Column C is the name of each item, Column D is the price of the item, Column E is where data will be entered with the index 1,2,3 to represent the order in which items are ordered. 

The first item’s price has to be from Column C. The price for the second item is fixed at 8 and third item 5. The customer might only order 1 item.

I need to add all these values together to give a total based on the conditions. 

What I expect the formula to do

The formula will first need to scan Column E to see if the index of 1,2 and 3 has been entered. If none is entered then the return value should be 0. If index 2 or 3 has been entered without index 1, then it should return ERROR.

For the value of index 1, the formula needs to look up the row in which the index is entered in column E then match its value from Column D and return that value.

For the value of index 2 (if there is one), the formula should return the value 8.

For the value of index 3 (if there is one), the formula should return the value 5.

The formula should add all the amount and give me a total value for the order. 

By: Lina Tan

Leave a Reply

Your email address will not be published. Required fields are marked *