Lookup formula?

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,375
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a table https://www.screencast.com/t/JtuMEa1Xp32, which has information that helps in generating quotes. I have multiple drop down lists allowing me to select the following information:
- The CSC is known, Customer service centre, and this allows me to determine if the location is remote, regional or metro. Stored on another tab is a table that has for every CSC in the list, whether it is remote, etc. The drop down, where the CSC is selected is situated at the top of the spreadsheet. The formula to the right side of the table, where it says Metropolitan is: =(INDEX(CSCs!G17:G99,MATCH(C4,CSC,0)))

The formula behid the Mon-fri cell near it is:
=IF(COUNTIF(Sheet2!G38:G48,B13),"Public Holiday",IF(WEEKDAY(B13,2)>5,"Weekend","Mon-Fri"))
This formula extracts from the date entered, if it is a public holiday, weekday or weekend.

The other columns for the table are as follows:
- Date
- What service or program, eg. Supervised contact, Supervised transport or Youth work support. (Drop down list)
- Hours taken
- Kilometres travelled
- Workers required.
- Price- this is the total.

I have worked out how to determine if the date is a public holiday, weekday or weekend automatically. I am just struggling a bit knowing which formula to use to extract the information from the table with the raw data, that needs to be multiplied by the hours and no of workers. Here is a picture of the table that I am trying to get the data from: https://www.screencast.com/t/QvJROWXzv9U I was going to use index and match but I didn't know how to make it refer to the data from sub headings. If anyone can think of a better way, can you let me know please or can you tell me how I am to do this with index and match?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,223,923
Messages
6,175,399
Members
452,640
Latest member
steveridge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top