Return value on multiple criteria without an exact match

Hi I do hope someone can help
I have a worksheet broken up into multiple road sections, each with its own ADT

Traffic
Road Road Name Start End ADT
563 ACOURT STREET 0 295 55
179 AHIPAIPA ROAD 0 2547 522
179 AHIPAIPA ROAD 2547 4027 344
179 AHIPAIPA ROAD 4027 4845 550


In another sheet I have a list of just over 2000 work sites, and I would like to populate this with the ADT for the site, taken from the road sections list
WORKSITES
Road Road Name Start Work ADT
179 AHIPAIPA ROAD 700 2 ?
179 AHIPAIPA ROAD 1858 R ?
179 AHIPAIPA ROAD 2192 R ?

For the example above the worksite that starts at 700 Ahipaipa Road would have an ADT of 522, as it is in the first section of Ahipaipa Road
I have tried various combinations of vlookup, match and index but could not seem to get anything to work

In the image shown below what I am trying to achieve is to import the data in column K into column E
For the worksite shown as starting at 700 Ahipaipa Road (row 6) to lookup the traffic data series for the ADT, ie
compare the worksite start in C6 to be greater than or equal to column I AND less than column J, but only where column G matches

So this would return the data in cell k5

Example.jpg

copy paste this on E4 :

=IFERROR(INDEX($K$4:$K$100,MATCH(1,(G4:G100=A6)*(C6>=$I$4:$I$100)*(C6<=$J$4:$J$100),0)),"")

and press F2 button, and you'll see the formula, then you need to confirm press/hit CTRL+SHIFT+ENTER all together, ENTER alone not workin... IF success you will "{}" bracket that enclosed the formula, this is array formula sign.

Final step copied formula in E4 after above step, down as necessary

If not workin, upload your small sample workbook, click "Go Advanced" button and find Paperclip Image Button to attach the file

regards

It does work, thank you so much Azumi
I just had to do a minor tweak and change the <= to a <