Looking to create formula to pull data based on multiple criteria and more

Hello!

If you will so kindly view my data set, I am trying to populate tab 1 with data from tab 2. I would like to be able to pull in Usage data (Tab 2, Col K) into tab 1 based on Building Code (Tab 2, Col B), Service Type (Tab 2, Col E) and Statement Date (Tab 2, Col H). The goal is to populate Tab 1 with this data so it can be update automatically by just uploading new underlying data and no manual editing necessary.

More info: -For Service Type, I only want "EL" code to be included in the calculation, any other codes should be ignored by the formula.
-I am trying to build this template by Building Code so only data relevant to one specific building should be being pulled (the example data set has 3 different building codes)
-I have totaled what the correct usage amount should be that the formula should populate in Tab 1, Cell G12 to try and help clarify a little and also give a check to know if the formula is correct.

Finally, I would like to add in a function using Data Validation or another method if it is easier to create a dropdown menu by Building Code (Tab 2, Col B) in Tab 1 so I can change between different buildings and have all the data change relevant to each building.

I know there is a lot here and if anything is unclear or I left anything out that is necessary to try and do this I apologize in advance. I have been trying to make something work on this for days and am just stuck so any help at all would be appreciated. Thanks so much for taking the time to help!

Sincerely,
BoneyLit

Excel Help 2.xlsx

Hi Glenn,

Thanks for the quick response! The file you attached was blank so not sure if you accidentally uploaded the wrong one or something. I tried the formula on my own and was having some issues so if you could please upload the file with your formula it would be much appreciated. Thanks!

Also, Columns M and O can be left blank for now. Appreciate it!!

Sincerely,
BoneyLit

No worries! I noticed C4 was empty and got it to populate but I thought it wasn't pulling the correct data but it is.

If you could also help with setting up data validation or whatever function allows me to create a drop down and change between Building Codes and have the template automatically populate accordingly, that would be wonderful. Appreciate all of your help!

Sincerely,
BoneyLit

Hi Glenn,

Thanks for this! I'm running into one more issue that I did not anticipate. For the "SEARCH("EL" part of the formula, is it possible to change it search for EL+#, i.e. EL1, EL2, etc. I ask because what I forgot to mention before is in my actual data set there are text next to these codes and some of the non-EL codes have words with "EL" in them (See Sheet 2, BETA11) so it is pulling in extra data into the formula that shouldn't be included. Could you help with this edit please? Thanks!

Sincerely,
BoneyLit