Generating Number Range Value - (INDEX SUMPRODUCT ROW?)

[RJB78]

Hello - I have lines of data (about 7,000) that contain a dollar value ranging from $0.00 to over $1,000. I was hoping to create a formula that calculate the $range the value is in. Example, if the cell value was $13.50, the formula would return $.01 - $25. $350, would return $300 - $400, $0 would return $0, if it was over $1000, would return >$1,000

Thank you in advance

The $ ranges I want to use are:

Range 1	Range 2	Value
$ -	$ -	$0.00
$ 0.001	$ 24.99	$0.01 - $25
$ 25.00	$ 49.99	$25 - $50
$ 50.00	$ 74.99	$50 - $75
$ 75.00	$ 99.99	$75 - $100
$ 100.00	$ 124.99	$100 - $125
$ 125.00	$ 149.99	$125 - $150
$ 150.00	$ 174.99	$150 - $175
$ 175.00	$ 199.99	$175 - $200
$ 200.00	$ 299.99	$200 - $300
$ 300.00	$ 399.99	$300 - $400
$ 400.00	$ 499.99	$400 - $500
$ 500.00	$ 749.99	$500 - $750
$ 750.00	$ 999.99	$750 - $1,000
$ 1,000.00	$ 10,000.00	>$1,000

 

[Toadstool]

Hi RJB78,

You could do a MATCH for less than or equal to:

RJB78.xlsx
	A	B	C	D	E
1	7k Numbers	Range Display		Range 1	Range to Display
2	$11.00	$0.001 - $25.00		($99,999.00)	$0.00
3	$0.00	$0.00		$0.00	$0.001 - $25.00
4	$24.00	$0.001 - $25.00		$25.00	$25.00 - $50.00
5	$25.00	$25.00 - $50.00		$50.00	$50.00 - $75.00
6	$26.00	$25.00 - $50.00		$75.00	$75.00 - $100.00
7	$122.00	$100.00 - $125.00		$100.00	$100.00 - $125.00
8	$1,111.00	>$1,000		$125.00	$125.00 - $150.00
9	$222.00	$200.00 - $300.00		$150.00	$150.00 - $175.00
10	$333.00	$300.00 - $400.00		$175.00	$175.00 - $200.00
11	$444.00	$400.00 - $500.00		$200.00	$200.00 - $300.00
12	$555.00	$500.00 - $750.00		$300.00	$300.00 - $400.00
13	$777.00	$750.00 - $1,000.00		$400.00	$400.00 - $500.00
14	$125.00	$125.00 - $150.00		$500.00	$500.00 - $750.00
15	$400.00	$400.00 - $500.00		$750.00	$750.00 - $1,000.00
16	$1,000.00	>$1,000		$1,000.00	>$1,000
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=INDEX($E$2:$E$16,MATCH(A2,$D$2:$D$16,1))

 

[RJB78]

Hi RJB78,

You could do a MATCH for less than or equal to:

RJB78.xlsx
	A	B	C	D	E
1	7k Numbers	Range Display		Range 1	Range to Display
2	$11.00	$0.001 - $25.00		($99,999.00)	$0.00
3	$0.00	$0.00		$0.00	$0.001 - $25.00
4	$24.00	$0.001 - $25.00		$25.00	$25.00 - $50.00
5	$25.00	$25.00 - $50.00		$50.00	$50.00 - $75.00
6	$26.00	$25.00 - $50.00		$75.00	$75.00 - $100.00
7	$122.00	$100.00 - $125.00		$100.00	$100.00 - $125.00
8	$1,111.00	>$1,000		$125.00	$125.00 - $150.00
9	$222.00	$200.00 - $300.00		$150.00	$150.00 - $175.00
10	$333.00	$300.00 - $400.00		$175.00	$175.00 - $200.00
11	$444.00	$400.00 - $500.00		$200.00	$200.00 - $300.00
12	$555.00	$500.00 - $750.00		$300.00	$300.00 - $400.00
13	$777.00	$750.00 - $1,000.00		$400.00	$400.00 - $500.00
14	$125.00	$125.00 - $150.00		$500.00	$500.00 - $750.00
15	$400.00	$400.00 - $500.00		$750.00	$750.00 - $1,000.00
16	$1,000.00	>$1,000		$1,000.00	>$1,000
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=INDEX($E$2:$E$16,MATCH(A2,$D$2:$D$16,1))

Thank worked. Thank you!

 

[Toadstool]

Thank worked. Thank you!

You're welcome.
Thanks for the feedback.