Select a row based on price being with a range (Min & Max Values)

WarrenH

Hello

Really hoping someone can help me with this. This works with a lookup formula in excel, but I can't seem to find a work around in smartsheet.

I need to take a figure, and look what price bracket/ band it falls into based on a table in another sheet.

So as an example:

Reference Sheet 1:

On a seperate sheet I would need to look up various calculated figures such as:

7 would return "Band 2"

14 would return "Band 3"

Etc, etc.

Glad of any help

PS Sorry I can't seem to comment on the reply below as still an observer (or just doing something wrong!) But my reply is:

Thanks, Alpha

There are hundreds of bands in the table, so I was hoping there was a way of doing it without using the If function and referencing each and every band! Very long formula otherwise and if we change the bands we will need to rewrite it all.

I had hoped to use the Index(Collect()) function but can't seem to work out how to use less than and more than within the criterion section.

Any other ideas would be gratefully received, otherwise, I will need to go down the route you suggest.

Many Thanks!

Alpha Chucky

Hi @WarrenH, the formula you are looking for is:

=IF(AND([Column2]@row >= 0, [Column2]@row <= 5), "Band 1", IF(AND([Column2]@row > 5, [Column2]@row <= 10), "Band 2", ""))

add all your additional conditions after the last comma, but before the "".

so your next set of conditions will look like this:

=IF(AND([Column2]@row >= 0, [Column2]@row <= 5), "Band 1", IF(AND([Column2]@row > 5, [Column2]@row <= 10), "Band 2", IF(AND([Column2]@row > 10, [Column2]@row <= 20), "Band 3", ""))

Let me know how it goes.

Good luck!

WarrenH

Thanks, Alpha

There are hundreds of bands in the table, so I was hoping there was a way of doing it without using the If function and referencing each and every band! Very long formula otherwise and if we change the bands we will need to rewrite it all.

I had hoped to use the Index(Collect()) function but can't seem to work out how to use less than and more than within the criterion section.

Any other ideas would be gratefully received, otherwise, I will need to go down the route you suggest.

Many Thanks!

SoS | Dan Palenchar

Interesting challenge here.

I envision some type of catered solution based on how you use the data.

If you literally enter ONLY integers (whole numbers) I could see restructuring the lookup sheet so that each row is acending numbers from 1 to the highest number you would input.

You could have a second column for the bands "Band" and add the correct number to each value.

You could then use a lookup formula to pull in the "Band" based on the number you entered.

If you define the process (what types of numbers go in) this could get refined to handle more nuance such as decimals.

Dan Palenchar | School of Sheets Solutions Consulting (Smartsheet Aligned Gold Partner)

Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

Smartsheet Tutorial Videos: schoolofsheets.com/youtube