Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Return rows from a table based on a numeric value

Copper Contributor

I'm trying to filter tables based on a numeric value entered by a user. Here's what I'm trying to do:

 

berryck_0-1702487621483.png

Hopefully that makes sense. 

 

5 Replies
Break column B in to 2 columns: min and max and then use FILTER(... , A2>=minCol, A2<=maxCol, "") Otherwise you need to apply text manipulation to break that text value 20-40 into those 2 columns I mention above.

@berryck 

Does the formula in the attached file return the intended result? I've made a simplification of the ranges 20 - 40 and so on in column B by separating them into 2 columns which is best practice in my opinion.

best response confirmed by berryck (Copper Contributor)
Solution

@berryck 

Try this one:

=LET(
    low, 1 * TEXTBEFORE(Table1[Range], "-"),
    high, 1 * TEXTAFTER(Table1[Range], "-"),
    FILTER(Table1, (input >= low) * (input <= high), "None found")
)

@OliverScheurich Brilliant! Thank you so much. I hadn't considered putting the values in different columns, that makes sense. 

@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.

image.png

1 best response

Accepted Solutions
best response confirmed by berryck (Copper Contributor)
Solution

@berryck 

Try this one:

=LET(
    low, 1 * TEXTBEFORE(Table1[Range], "-"),
    high, 1 * TEXTAFTER(Table1[Range], "-"),
    FILTER(Table1, (input >= low) * (input <= high), "None found")
)

View solution in original post