#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:

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.

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!! 🚀✨⬇️

✨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

Comments

[Judith-Excel-Sharing]

Transform how you handle data intervals! 📊

Discord
Reddit