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

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

5 Replies

# Re: Return rows from a table based on a numeric value

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.

# Re: Return rows from a table based on a numeric value

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

# Re: Return rows from a table based on a numeric value

Try this one:

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

# Re: Return rows from a table based on a numeric value

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

# Re: Return rows from a table based on a numeric value

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.

1 best response

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

# Re: Return rows from a table based on a numeric value

Try this one:

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