Example 1:
Here below is a data table:

The task is to calculate the values in column B of Sheet2 according to the rule: search Sheet1 with the quantity value of Sheet2, if the value is greater than StartQuantity and less than or equal to EndQuantity of a certain row, return the price of this row. To achieve this task, enter in cell B2:

=spl("=E(?1).segp@r(StartQuantity,?2).Price",Sheet1!A$1:C$5,A2)

Then drag B2 down to every relevant row:

The idea of the calculation is to use the segp function to query which segment number of interval formed by StartQuantity of Sheet1 the quantity value is in, and then take the price of the row corresponding to the segment number and return. The option @r means forming a left-open and right-closed interval. For example, the number 50 should be counted in the interval where the first row is located.

Example 2:
Here below is a car charging data table:

The following table lists the electricity price data at different charging time intervals:

The task is to calculate the values in Price column of Sheet3 according to the rule: search Sheet4 for the time interval where the hour number of Starttime is located, and take the price. To achieve this, enter in cell E2:

=spl("=a=E@b(?1),a(3).array().to(2,)(a(1).array().to(2,).pseg(?2))", Sheet4!A$1:F$3, HOUR(B2))

Drag E2 down to every relevant row:

The idea of calculation is to use the StartHour sequence starting from the 1st row and the 2nd column of Sheet4 to form the time intervals, and search for the interval where the hour number of Starttime of Sheet3 is located, and take the price in the corresponding 3rd row of Sheet4 and return.

esProc Desktop and Excel Processing
12.5 Use formulas to handle interval association
12.7 Use a two-dimensional association table