Forum Discussion
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.
Try this one:
=LET( low, 1 * TEXTBEFORE(Table1[Range], "-"), high, 1 * TEXTAFTER(Table1[Range], "-"), FILTER(Table1, (input >= low) * (input <= high), "None found") )
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.
Try this one:
=LET( low, 1 * TEXTBEFORE(Table1[Range], "-"), high, 1 * TEXTAFTER(Table1[Range], "-"), FILTER(Table1, (input >= low) * (input <= high), "None found") )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.
OliverScheurich Brilliant! Thank you so much. I hadn't considered putting the values in different columns, that makes sense.
- 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.
