Creating a formula to calculate a result that depends on 3/4 criterias been met

Firstly its probably best you download the file from below and open it up.

http://www.mediafire.com/?un3smhmdyzt

I need a formula to put in cells in column S on sheet March.

It needs to match up the correct price from sheet 'Container Price List'.

The price that should be displayed depends firstly on the account number, container and waste stream all matching to decipher which line the price is on. Then the price displayed should be the price in the column called 'exchange charge' however if this is 0 it should be the price in the column called 'rental charge'.

So for example, if you look at row 27 in sheet 'March' the price displayed would be £80.39. The formula will look at the account number 'c028' on sheet 'March' and match it to the any of 'c028' on sheet 'Container Price List'. It will then look at the container in sheet 'March' which is RL14 and match it with the ones that are RL14 in sheet 'Container Price List'. Then it will look at the Waste Stream in sheet 'March' which is GEN.C and match it with the GEN.C in sheet 'Container Price List'. The result given should be the exchange charge in sheet 'Container Price List' which in this case is £80.39.

If for example the exchange charge displayed £0.00 then the result given should be the rental charge in sheet 'Container Price List' instead.

Best of luck!

arh yes forgot about that. Your damn quick btw!

If you type in MAIN STORES in S27 and add another match on the end of all the others matches that should sort it. So that the order to match is account number, container waste stream and whatever is in S27 to match with column B in sheet 'container price list' that will then give the right price from the correct row.

I'll have to make sure that Column S in sheet March is amended then for all rows.

Sorry not doing very well today. You'd have to insert another column on sheet 'March' in between Container and Lift £ and then do the above^

This seems to get more and more complicated. Firstly download this spreadsheet.

http://www.mediafire.com/?mtwbxttmxwt

I need to edit the formula on sheet ‘March’ in column T that shows the Lift £.

At the moment it shows the relevant price depending on certain conditions been met.

For example on row 1448 it shows in column T the price of £1,351.46. To determine this the formula has looked at the data on sheet ‘Container Price List’ and matched up the appropriate cell by looking at the Account number, Waste Type, Bin Type and Location (called Site Name on sheet ‘Container Price List’). The price displayed is the exchange price on sheet ‘Container Price List’ however; if that equals 0 then the price for Rental is shown instead (this been the case for cell T1448).

Now the problem is it doesn’t take into account the number of lifts (exchanges). For example in cell T1474 the price for 1 exchange is shown at £80.39. The problem is there were 2 exchanges (shown in cell Q1474). Therefore the price in T1474 should be multiplied by cell Q1474.

Now there is a twist because if the price is a rental price as in T1448 it shouldn’t be multiplied by Q1448. Instead it should look in sheet ‘Container Price List’ and the relevant cell in column J which in this instance is J206 and perform the calculation J206*4.33/the rental price in H206

Yep that works! Thank you