#132 — Use Interval Range to Perform Retroactive Searching of Association Table
Problem description & analysis: Here below is a data table: Task: Calculate the values in column D of Sheet1 according to the following requirements: The ID column of Sheet2 is the same as that of Sheet1. When the first criterion is met, judge whether the Num of Sheet2 falls into the interval between start and end of Sheet1. When the above two criteria are met at the same time, the value in Sheet1 is the corresponding value in Sheet2. Solution: Use SPL XLL and enter the following code in cell D2: =spl("=E(?1).select@1(ID==?2 && Num>?3 && Num
Problem description & analysis:
Here below is a data table:
Task: Calculate the values in column D of Sheet1 according to the following requirements:
- The ID column of Sheet2 is the same as that of Sheet1.
- When the first criterion is met, judge whether the Num of Sheet2 falls into the interval between start and end of Sheet1.
- When the above two criteria are met at the same time, the value in Sheet1 is the corresponding value in Sheet2.
Solution:
Use SPL XLL and enter the following code 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:
Download esProc Desktop for FREE and eliminate manual errors using SPL XLL!!
