Formula to extract a value based on a client code match AND that fits within a date range

Hi guys,

I got two tables, in the attached file.

I need to pull the value of units in Table 1 (Column L in Sheet1) into column H in Table 2 (Sheet 2).
The number to pull will depend on:
- The Student ID and the Subject match
- The Service End in Table 2 (Column E) falls within the "Term" date range in Table 1 (from Dates in Columns G to Dates in Column H)



If the criteria is not meet, could the formula display a message like "Code not found"?




Please help!!!

This is what I have come up with so far. I am still trying to figure out how to handle your 2nd criteria with the dates.
Maybe someone can help figure this out. Will try and work on it again if no one picks it up.

=IFERROR(INDEX(Sheet1!$L$2:$L$50,MATCH(A2&B2,Sheet1!$A$2:$A$50&Sheet1!$D$2:$D$50,0)),"Code not found")
Note it is an array - so you have to do CTRL+SHIFT+ENTER to ensure it works properly.
Also - If you are going to have more than 50 entries on Sheet 1 just change the formula from 50 (in 3 places) to 999 for example.

FYI - I found an extra space after Literacy on Sheet 2 (row 34 or 35) so make sure extra spaces aren't in that column at the end or the formula won't pick it up.

Let me know if you have any questions.