What would be the best formula?

[dpaton05]

I have a spreadsheet that looks like this, https://www.screencast.com/t/EhxupL7A. The spreadsheet is for developing quotes. CSC stands for community service centre and is a drop down list next to it allowing you to chose the CSC. Each CSC will be in a different location, remote, regional or metropolitan. That then affects the price that workers are paid for a service in that area, for instance, remote areas might attract a higher rate then regional areas. The date is when it is performed and will also change the price of a service, for instance, workers will receive more on a weekend and more again on a public holiday. I have a list of public holidays and I have worked out how to have them all based on formulas so the dates of them calculate each time the spreadsheet is opened. The service is a drop down list with about 8 activities the worker could perform. These will all have different effects on the price. Here is a picture of the contents of that list https://www.screencast.com/t/vdVKdBOW4.

Then there is hrs taken and kms travelled fields. This is followed by a total field.

I am trying to adapt it to use instead of this one my manager is using and he wanted me to simplify it so there is less inputs. https://www.screencast.com/t/wSocY3UjzShi

Here is a picture of some of some of the items from the deliverables list of the original spreadsheet https://www.screencast.com/t/3HTfH0bo

This is a picture of some of the items from a Services price list table in another tab. https://www.screencast.com/t/9lFB3tYMBeB

This is the main formula that is used to calculate the price on the original spreadsheet =IF(OR(ISBLANK(A4),ISBLANK(C4),ISBLANK(D4)),0,VLOOKUP(CONCATENATE(A4," ",C4," ",D4),Service_Price_List,2,FALSE))


What I need to know if what would be a good formula I could use for this new spreadsheet. I have all the tables and everything from the original spreadsheet in the new spreadsheet, just not so many fields to enter information. He wants me to program it so it figures out most things by itself without needing to enter.

Could someone help me please with an appropriate formula that would be efficient in doing this. I don't know if I have added enough info on here about it for anyone to determine the best formula to use, if I need to add more, let me know. I originally was thinking I could use many nested Ifs as I am not that familiar with formulas in excel but that would be very messy.

Thank you,
Dave

 

[Dave Patton]

The last example s/b just

Excel 2010
	B	C	D	E
17	B	NW	Work-z	1,000.00
18	A	B	C	375.00
1e

Cell Formulas
Range	Formula
E17	=VLOOKUP(D17,INDIRECT("r"&B17&C17),2,0)
E18	=VLOOKUP(D18,INDIRECT("r"&B18&C18),2,0)