Forum Discussion

berryck's avatar
berryck
Copper Contributor
Dec 13, 2023
Solved

Return rows from a table based on a numeric value

I'm trying to filter tables based on a numeric value entered by a user. Here's what I'm trying to do:   Hopefully that makes sense.   
excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Dec 13, 2023

    berryck 

    Try this one:

    =LET(
    low, 1 * TEXTBEFORE(Table1[Range], "-"),
    high, 1 * TEXTAFTER(Table1[Range], "-"),
    FILTER(Table1, (input >= low) * (input <= high), "None found")
)
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 13, 2023

berryck 

I posted in a rush during a meeting break.  This is a little more detailed

= LET(
    Betweenλ,   LAMBDA(bounds, XOR(number<=VALUE(TEXTSPLIT(bounds,"-")))),
    isInRange?, MAP(range, Betweenλ),
    FILTER(table, isInRange?, "Out of range")
  )

The ranges are defined with hyphens and converted to pairs of values by TEXTSPLIT.  The condition for the number to lie within the bounds is that it must be greater than the lower whilst less than the upper, hence XOR.  The Lambda function only works with a single range so the function Betweenλ is called using MAP to feed the ranges one at a time.  The Boolean array is used as the criterion range in FILTER.

Resources