Example 1:

Problem description & analysis:
Here below is a data table:

Task: 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.

Solution:

To achieve this task, use SPL XLL and enter the following code 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:

Code explanation:

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:

Problem description & analysis:
Here below is a car charging data table:

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

Task: 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.

Solution:
To achieve this, use SPL XLL and enter the following code 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:

Code explanation:

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.

Download esProc Desktop for FREE and revolutionize your Excel processes using SPL XLL!! 🚀✨⬇️

✨SPL download address: esProc Desktop FREE Download

✨Plugin Installation Method: SPL XLL Installation and Configuration

✨References to other rich Excel operation cases: Desktop and Excel Data Processing Cases

✨YouTube FREE courses: SPL Programming