Here below is a data table:

The task is to calculate the values in column D of Sheet1 according to the following requirements:
1. The ID column of Sheet2 is the same as that of Sheet1.
2. When the first criterion is met, judge whether the Num of Sheet2 falls into the interval between start and end of Sheet1.
3. When the above two criteria are met at the same time, the value in Sheet1 is the corresponding value in Sheet2.
Enter in cell D2:

=spl("=E(?1).select@1(ID==?2 && Num>?3 && Num <=?4).Value",Sheet2!A$1:C$5,A2,B2,C2)

Then drag D2 down to every relevant row:

esProc Desktop and Excel Processing
12.7 Use a two-dimensional association table
12.9 Associate multiple rows of data