Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 99
  • Last Modified:

Formula to calculate outcome based on number range

I have a range of numbers which I need to compare against a table to determine an output. So, for example the table has 7 different options, less than 1000 (equals 1), between 1001-2000 (equals 2), between 2001-3000 (equals 3), etc and so when we input numbers, for example 2431, the output would automatically determine the answer is 3.

I know its a lookup but I dont know how to do a range lookup. I could just do a huge IF formula but I know there is a better way.

I have attached the spreadsheet im working so it is clearer and perhaps the solution can just be put directly into the spreadsheet.

Thanks
Discount-Model.xlsx
0
recycleaus
Asked:
recycleaus
1 Solution
 
Rory ArchibaldCommented:
You can put the start values of each band into a column and then use those in the LOOKUP formula. See attached version of your workbook. I'm not sure what you expect to happen with the national rate column.
Discount-Model.xlsx
0
 
recycleausAuthor Commented:
Perfect... knew it would be an easy solution for someone!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now