Help on putting a number into a range

[tana]

Hi,
Can someone help me with this? I am trying to put a number into corresponding range. My range table is like this:

Bonus Rating Template - Master Dummy Test a.xlsx
	J	K
2	0	0-84
3	85	85-94
4	95	95-104
5	105	105-114
6	115	115-124
7	125	125-134
8	135	135+
Input

When I put each number into the range, it seems to have problems starting at 115.

Bonus Rating Template - Master Dummy Test a.xlsx
	DB	DC
2	20	0-84
3	20	0-84
4	20	0-84
5	20	0-84
6	20	0-84
7	20	0-84
8	20	0-84
9	40	0-84
10	40	0-84
11	40	0-84
12	40	0-84
13	40	0-84
14	60	0-84
15	60	0-84
16	60	0-84
17	60	0-84
18	60	0-84
19	60	0-84
20	60	0-84
21	60	0-84
22	60	0-84
23	60	0-84
24	60	0-84
25	60	0-84
26	60	0-84
27	60	0-84
28	60	0-84
29	60	0-84
30	75	0-84
31	75	0-84
32	80	0-84
33	80	0-84
34	80	0-84
35	80	0-84
36	80	0-84
37	80	0-84
38	80	0-84
39	80	0-84
40	80	0-84
41	80	0-84
42	85	85-94
43	85	85-94
44	85	85-94
45	85	85-94
46	85	85-94
47	85	85-94
48	85	85-94
49	85	85-94
50	85	85-94
51	85	85-94
52	85	85-94
53	95	95-104
54	95	95-104
55	95	95-104
56	95	95-104
57	95	95-104
58	95	95-104
59	95	95-104
60	95	95-104
61	95	95-104
62	95	95-104
63	95	95-104
64	95	95-104
65	95	95-104
66	95	95-104
67	95	95-104
68	95	95-104
69	95	95-104
70	95	95-104
71	95	95-104
72	95	95-104
73	95	95-104
74	95	95-104
75	95	95-104
76	95	95-104
77	95	95-104
78	95	95-104
79	95	95-104
80	100	95-104
81	100	95-104
82	100	95-104
83	100	95-104
84	100	95-104
85	100	95-104
86	100	95-104
87	100	95-104
88	100	95-104
89	100	95-104
90	100	95-104
91	100	95-104
92	100	95-104
93	100	95-104
94	100	95-104
95	100	95-104
96	100	95-104
97	100	95-104
98	100	95-104
99	100	95-104
100	100	95-104
101	100	95-104
102	100	95-104
103	100	95-104
104	100	95-104
105	100	95-104
106	100	95-104
107	100	95-104
108	100	95-104
109	100	95-104
110	100	95-104
111	100	95-104
112	100	95-104
113	100	95-104
114	100	95-104
115	100	95-104
116	100	95-104
117	100	95-104
118	100	95-104
119	100	95-104
120	100	95-104
121	100	95-104
122	100	95-104
123	105	105-114
124	105	105-114
125	105	105-114
126	105	105-114
127	105	105-114
128	105	105-114
129	105	105-114
130	105	105-114
131	110	105-114
132	110	105-114
133	110	105-114
134	110	105-114
135	110	105-114
136	110	105-114
137	110	105-114
138	110	105-114
139	110	105-114
140	110	105-114
141	110	105-114
142	110	105-114
143	110	105-114
144	110	105-114
145	110	105-114
146	110	105-114
147	110	105-114
148	110	105-114
149	110	105-114
150	110	105-114
151	110	105-114
152	110	105-114
153	110	105-114
154	110	105-114
155	110	105-114
156	110	105-114
157	110	105-114
158	110	105-114
159	110	105-114
160	110	105-114
161	110	105-114
162	110	105-114
163	115	105-114
164	115	105-114
165	115	105-114
166	115	105-114
167	115	105-114
168	115	105-114
169	115	105-114
170	115	105-114
171	115	105-114
172	115	105-114
173	115	105-114
174	115	105-114
175	115	105-114
176	115	105-114
177	115	105-114
178	115	105-114
179	115	105-114
180	115	105-114
181	115	105-114
182	115	105-114
183	115	105-114
184	115	105-114
185	115	105-114
186	115	105-114
187	115	105-114
188	115	105-114
189	115	105-114
190	115	105-114
191	115	105-114
192	115	105-114
193	115	105-114
194	115	105-114
195	115	105-114
196	115	105-114
197	115	105-114
198	115	105-114
199	115	105-114
200	115	105-114
201	115	105-114
202	115	105-114
203	115	105-114
204	115	105-114
205	115	105-114
206	115	105-114
207	115	105-114
208	120	115-124
209	120	115-124
210	120	115-124
211	120	115-124
212	120	115-124
213	120	115-124
214	120	115-124
215	120	115-124
216	120	115-124
217	120	115-124
218	120	115-124
219	120	115-124
220	120	115-124
221	120	115-124
222	120	115-124
223	120	115-124
224	120	115-124
225	120	115-124
226	120	115-124
227	120	115-124
228	120	115-124
229	120	115-124
230	120	115-124
231	120	115-124
232	120	115-124
233	120	115-124
234	120	115-124
235	120	115-124
236	120	115-124
237	120	115-124
238	120	115-124
239	120	115-124
240	120	115-124
241	120	115-124
242	120	115-124
243	120	115-124
244	120	115-124
245	130	125-134
246	130	125-134
247	135	135+
248	145	135+
249	145	135+
250	145	135+
251	145	135+
252	145	135+
253	145	135+
254	145	135+
255	145	135+
256	145	135+
257	145	135+
258	145	135+
259	145	135+
260	145	135+
261	145	135+
262	145	135+
263	145	135+
264	145	135+
265	145	135+
266	145	135+
267	145	135+
268	145	135+
269	145	135+
270	145	135+
271	145	135+
272	145	135+
273	145	135+
274	145	135+
SortData

Cell Formulas
Range	Formula
DB2:DB274	DB2	=DA2#*100
DC2:DC274	DC2	=XLOOKUP(DB2#,Input!$J$2:$J$8,Input!$K$2:$K$8,,-1)
Dynamic array formulas.

I expected 115 is in the range of "115-124" rather than "105-114", I have tried to use vlookup as well, but same issue. Thanks!

 

[tana]

Great discussion! And great suggestions as well.

Peter's initial suggestion =VLOOKUP(ROUND(DB2#,10),Input!J2:K8,2) works.

Thanks for all the help!!! Appreciated!

 

[Holger]

No, if you did what I suggested in your yellow cell and now get the wrong answer, try this.
Select that yellow cell with the formula, Copy it and use Paste Special (Values) to put the value back in the same yellow cell where the formula was.
The cell now contains a number, not a formula, but the result is still incorrect.

It is about how Excel, running on a machine, does not and cannot always do everything exactly as a human would do. See here for some further reading.
Also look at (& try for yourself) the 3 calculations shown in this post that all give exactly the same result if I do them manually myself, but not when Excel does them.

Rounding the input cell will give a correct result....

find bracket.xlsx
	D	E	F	G
10	achievement	115	bracket	115-124
11		0	85	0-84
12		85	95	85-94
13		95	105	95-104
14		105	115	105-114
15		115	125	115-124
16		125	135	125-134
Input

Cell Formulas
Range	Formula
G10	G10	=VLOOKUP(E10,E11#,3,TRUE)
E10	E10	=ROUND(1.15*100,0)
E11:G16	E11	=LET( achieve,E10, bonusbracket, B1:B7, upper,A1:A7, lower,DROP(VSTACK(0,upper),-1), DROP(HSTACK(lower, upper, DROP(VSTACK(0,bonusbracket),-1)),1) )
Dynamic array formulas.

 

[Peter_SSs]

Rounding the input cell will give a correct result....

Yes, that's why I suggested it back in post #13.

Great discussion! And great suggestions as well.

Peter's initial suggestion =VLOOKUP(ROUND(DB2#,10),Input!J2:K8,2) works.

Thanks for all the help!!! Appreciated!

You are welcome. Thanks for the follow-up.