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!

 

[kevin9999]

Could you show what you have in the DA column on the SortData sheet?

 

[6StringJazzer]

Too bad you didn't include DA. I can't paste your formulas.

I cannot reproduce your problem. I suspect that your numbers are displayed as integers, but are not really integers. So if you have a value like 114.99999999 it is going to show up as 105-114.

$scratch.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Integers			Not Integers			Not Integers but Formatted as Integers
2	105	105-114		104.999	95-104		105	95-104		0	0-84
3	106	105-114		105.999	105-114		106	105-114		85	85-94
4	107	105-114		106.999	105-114		107	105-114		95	95-104
5	108	105-114		107.999	105-114		108	105-114		105	105-114
6	109	105-114		108.999	105-114		109	105-114		115	115-124
7	110	105-114		109.999	105-114		110	105-114		125	125-134
8	111	105-114		110.999	105-114		111	105-114		135	135+
9	112	105-114		111.999	105-114		112	105-114
10	113	105-114		112.999	105-114		113	105-114
11	114	105-114		113.999	105-114		114	105-114
12	115	115-124		114.999	105-114	==>	115	105-114
13	116	115-124		115.999	115-124		116	115-124
14	117	115-124		116.999	115-124		117	115-124
15	118	115-124		117.999	115-124		118	115-124
Input

Cell Formulas
Range	Formula
A2:A15	A2	=SEQUENCE(14,1,105,1)
B2:B15,H2:H15,E2:E15	B2	=XLOOKUP(A2#,Input!$J$2:$J$8,Input!$K$2:$K$8,,-1)
D2:D15,G2:G15	D2	=SEQUENCE(14,1,105,1)-0.001
Dynamic array formulas.

 

[tana]

Could you show what you have in the DA column on the SortData sheet?

Bonus Rating Template - Master Dummy Test.xlsx
	DA	DB	DC
2	0.05	5	0-84
3	0.05	5	0-84
4	0.05	5	0-84
5	0.05	5	0-84
6	0.05	5	0-84
7	0.05	5	0-84
8	0.2	20	0-84
9	0.2	20	0-84
10	0.2	20	0-84
11	0.2	20	0-84
12	0.2	20	0-84
13	0.2	20	0-84
14	0.2	20	0-84
15	0.2	20	0-84
16	0.2	20	0-84
17	0.2	20	0-84
18	0.2	20	0-84
19	0.2	20	0-84
20	0.2	20	0-84
21	0.2	20	0-84
SortData

Cell Formulas
Range	Formula
DA2:DA1922	DA2	=IF(OR('Rpt 2 V'!B2="",'Rpt 2 V'!B4=""), CE2#, DROP(CHOOSECOLS(CQ2#,8),1))
DB2:DB1922	DB2	=DA2#*100
DC2:DC1922	DC2	=VLOOKUP(DB2#,Input!J2:K8,2)
Dynamic array formulas.

DA column is another spilled array. It is one of column from an array. Thanks,

 

[tana]

Could you show what you have in the DA column on the SortData sheet?

If you like, I can share my file with you? Please let me know how I can share it to you. Thanks for your help!!!

 

[kevin9999]

That would be good - best to use a file sharing platform such as Dropbox, Google Drive etc. Just remeber to share the file with anyone with the link - and post the link here.
thank you.

 

[tana]

@kevin9999

Sorry, I guess I can't share the file. I tried to use nested IF and it works now, but just a bit complicated. Thanks for your help.
I wish I can share the file with you so to know the truth of this issue. Actually, based on your previous comment, I did go back to the original data and ensure that was not a text. The result stayed the same, no change, no fix.

 

[Cubist]

Double check values in J2:J8 to make sure they're not floating decimals.

 

[tana]

Double check values in J2:J8 to make sure they're not floating decimals.

checked it, all integers and no decimals. Thanks for your suggestions.

 

[Cubist]

Last thing I can suggest is to check if the a 115 in DA = Input!J6. Does it return TRUE or FALSE?