Number value range comparison against single number

[sanfrancisco]

What is the best way to compare a large set of numbers against a single value. There are 10 cells which have a final value that changes dynamically with user input. Trying to compare this cell against the value range below. I tried a nested IF(AND..) but excel kicked back an error saying there are too many nested IF's so that option is out. Here are the values i'm comparing against the value range:

VALUES (To test against Value Range)
[TABLE="width: 59"]
<tbody>[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[/TR]
[TR]
[TD="align: right"]245[/TD]
[/TR]
[TR]
[TD="align: right"]352[/TD]
[/TR]
[TR]
[TD="align: right"]357[/TD]
[/TR]
[TR]
[TD="align: right"]379[/TD]
[/TR]
[TR]
[TD="align: right"]406[/TD]
[/TR]
</tbody>[/TABLE]

VALUE RANGE (between 0 and 1000 with following intervals to evaluate)
[TABLE="width: 76"]
<tbody>[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]51[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD="align: right"]76[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD="align: right"]151[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]201[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD="align: right"]251[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD="align: right"]301[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD="align: right"]401[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD="align: right"]501[/TD]
[TD="align: right"]600[/TD]
[/TR]
[TR]
[TD="align: right"]601[/TD]
[TD="align: right"]700[/TD]
[/TR]
[TR]
[TD="align: right"]701[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD="align: right"]801[/TD]
[TD="align: right"]900[/TD]
[/TR]
[TR]
[TD="align: right"]901[/TD]
[TD="align: right"]1000[/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

If the Value Range is A1:B14

=MATCH(12, A1:A14) will tell you which interval the number 12 is in.

 

[martindwilson]

i take it you need to check each of those and see which range they fall in?

Excel Workbook
	A	B	C	D	E	F
1	number	which range is it in?	lower of range	upper of range	range name
2	0	0-50	0	50	0-50
3	36	0-50	51	75	51-75
4	50	0-50	76	100	76-100
5	95	76-100	101	150	101-150
6	245	201-250	151	200	151-200
7	352	301-400	201	250	201-250
8	357	301-400	251	300	251-300
9	379	301-400	301	400	301-400
10	406	401-500	401	500	401-500
11	51	51-75	501	600	501-600
12	50	0-50	601	700	601-700
13	1000	901-1000	701	800	701-800
14	1001	out of range	801	900	801-900
15			901	1000	901-1000
16	401	401-500	1001		out of range
Sheet1

 

[sanfrancisco]

This formula seems to work the best. Here is what I am using in my sheet: =LOOKUP((D20-$D$20),$L$2:$L$15,$M$2:$M$15). My results are shown below and I need to be able to use the multiple from that range in my final algorithm so essentially if that range is 0-50 then i need the final value to return the third number which is the RETURN NUMBER:
LOWER / UPPER / RETURN NUMBER
[TABLE="width: 122"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"].25%[/TD]
[/TR]
[TR]
[TD="align: right"]51[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"].50%[/TD]
[/TR]
[TR]
[TD="align: right"]76[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1%[/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]2%[/TD]
[/TR]
[TR]
[TD="align: right"]151[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]2.5%[/TD]
[/TR]
[TR]
[TD="align: right"]201[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]3%[/TD]
[/TR]
[TR]
[TD="align: right"]251[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]4%[/TD]
[/TR]
[TR]
[TD="align: right"]301[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]401[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]7%[/TD]
[/TR]
[TR]
[TD="align: right"]501[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]9%[/TD]
[/TR]
[TR]
[TD="align: right"]601[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]10%[/TD]
[/TR]
[TR]
[TD="align: right"]701[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]13%[/TD]
[/TR]
[TR]
[TD="align: right"]801[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]15%[/TD]
[/TR]
[TR]
[TD="align: right"]901[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]16%[/TD]
[/TR]
</tbody>[/TABLE]

Spreadsheet Formulas

Cell Formula B2 =IF(A2="","",LOOKUP(A2,$C$2:$C$16,$E$2:$E$16)) <tbody> </tbody>

<tbody>
</tbody>

VALUES RETURNED WITH ABOVE FORMULA:
[TABLE="width: 59"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD="align: right"]500[/TD]
[/TR]
</tbody>[/TABLE]

 

[martindwilson]

instead of range name like
[FONT=Calibri, Arial]0-50[/FONT]
[FONT=Calibri, Arial]put the percentages in that column you really don't need upper any way
or just change the formula to retrieve the correct column

[/FONT]=LOOKUP((D20-$D$20),$L$2:$L$15,$N$2:$N$15)
where column L has the lower limit and column N the percentages