DEV Community

Cover image for #32 — Find The First Cell Value Meeting The Specified Condition
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

#32 — Find The First Cell Value Meeting The Specified Condition

Problem description & analysis:

In the table below, the 1st column is subject and columns after it are test scores arranged in order.

original table

Task: Specify a subject and a score and, according to the specified values, find the first cell value that is equivalent to or greater than the score under the subject. For example, when the parameters are Maths and 6.5, the expected return result is 6.6.

Solution:

Use SPL XLL to do this:



=spl("=?1.select@1(~1==?2).m(2:).select@1(~>=?3)",A1:E3,"Maths",6.5)


Enter fullscreen mode Exit fullscreen mode

As shown in the picture below:

result table with code entered
Explanation:

select@1 function find the first eligible member that meets the condition. m(2:) gets members from the 2ndto the last. ~1 is the 1st member and ~ represents the current member.

Top comments (2)

Collapse
 
judith677 profile image
Judith-Excel-Sharing

Feel free to download it and explore the data processing journey on your own⬇️
💡SPL download address: scudata.com/download-Desktop
💡Plugin Installation Method: c.scudata.com/article/1652061135502
💡References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524
💡SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...

Collapse
 
judith677 profile image
Judith-Excel-Sharing

If you found this useful and want to see more productivity hacks or have any Excel tasks that you’d like us to tackle, feel free to reach out:
💭Discord: discord.gg/hgbKEvJ4
💭Reddit: reddit.com/r/esProc_Desktop/

Your problem might be featured in our next Excel hack series!