Select a row based on price being with a range (Min & Max Values)
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!
Answers

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!
Projects Delivered. Data Defended.

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!

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
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
Help Article Resources
Categories
Check out the Formula Handbook template!