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
👨🏼💻 Dan Palenchar  School of Sheets Solutions Consulting  Smartsheet Aligned Gold Partner
If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!
 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
 ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube
PS  If you have a follow up response tag me @SoS  Dan Palenchar so I get notified of your reply!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!