Solved: Look up value between two numbers

Arizno · 11-12-2019 01:34 AM

I have a column with square footage numbers and next to it is the results. I am trying to lookup my “inputed” square footage numbers to see where it falls in my list of square footage numbers so i can extract the results.

What is the best way to go about that?

Fernando_Lopez1

You need to reestructure your Fee table:
Something like this:

So the Expresion formula will be:

=ANY(SELECT(FEE_TABLE[Removal Cost] , AND ( [Your_Data] >= [Removal SqFt Min], [Your_Data] <= [Removal SqFt Max] ) ) )

Hope it helps

View solution in original post

jcadence

Hi @Arizno! I think what you’re looking for is a fixed list of options which would be our “lookup/dropdown” functionality. Here’s a support article to help walk you through setting it up.

Arizno

No not what I was looking for. For example someone inputs 200 sqft in the form. I have a column that need to calculated the fee for removing existing flooring. I have a two part column with a fee structure depending upon the square footage.

Here is an example of the table:

If someone enters 200 the removals cost would be the the first row since it is less than 300. The value I am trying to acheive is “square footage” (that they entered in the form) multiplied by the “Removals cost” that is applicable. In this case it would be 4.

Anything above the last row item would just fall on the last row cost.

Fernando_Lopez1

You need to reestructure your Fee table:
Something like this:

So the Expresion formula will be:

=ANY(SELECT(FEE_TABLE[Removal Cost] , AND ( [Your_Data] >= [Removal SqFt Min], [Your_Data] <= [Removal SqFt Max] ) ) )

Hope it helps

Arizno

Worked like a charm. Thank you my good sir! Much appreciate it.

Fernando_Lopez1

You’re welcome