Formula to get highest value between two numbers

[aliaslamy2k]

Dear Experts,

I want to formula to display the highest number in between two numbers.

Example :
1. In Cell AQ8 display highest between 30 to 45 else blank
2. In Cell AQ9 Display highest number between 10 to 30 else blank
3. In Cell AQ10 Display highest number between 0 to 10 else blank

Book1
	AK	AL	AM	AN	AO	AP	AQ	AR
5	Licence Expiration	LIC Days Remaining
6	18-Dec-96	LIC EXPIRED
7	23-Apr-15	LIC EXPIRED			Greater Than	Less than	Result	Remarks
8	13-Apr-17	LIC EXPIRED			30	45	44	To check in column AL and get the result in AQ8
9	19-Jul-17	LIC EXPIRED			10	30	29	To check in column AL and get the result in AQ9
10	25-Jan-18	LIC EXPIRED			0	10	9	To check in column AL and get the result in AQ10
11	14-Mar-18	LIC EXPIRED
12	16-Oct-18	LIC EXPIRED
13	4-Nov-18	LIC EXPIRED
14	4-Nov-18	LIC EXPIRED
15	9-Jan-19	LIC EXPIRED
16	2-Apr-19	LIC EXPIRED
17	9-Jun-19	LIC EXPIRED
18	20-Jul-19	LIC EXPIRED
19	25-Jul-19	LIC EXPIRED
20	28-Jul-19	LIC EXPIRED
21	28-Jul-19	LIC EXPIRED
22	28-Jul-19	LIC EXPIRED
23	28-Jul-19	LIC EXPIRED
24	13-Oct-19	LIC EXPIRED
25	20-Oct-19	LIC EXPIRED
26	20-Oct-19	LIC EXPIRED
27	14-Nov-19	LIC EXPIRED
28	14-Nov-19	LIC EXPIRED
29	5-Feb-20	LIC EXPIRED
30	16-Feb-20	LIC EXPIRED
31	19-Feb-20	LIC EXPIRED
32	2-Mar-20	LIC EXPIRED
33	9-Mar-20	LIC EXPIRED
34	18-Mar-20	LIC EXPIRED
35	23-Mar-20	LIC EXPIRED
36	23-Mar-20	LIC EXPIRED
37	25-Mar-20	1
38	26-Mar-20	2
39	26-Mar-20	2
40	26-Mar-20	2
41	26-Mar-20	2
42	26-Mar-20	2
43	26-Mar-20	2
44	26-Mar-20	2
45	26-Mar-20	2
46	26-Mar-20	2
47	26-Mar-20	2
48	26-Mar-20	2
49	26-Mar-20	2
50	26-Mar-20	2
51	26-Mar-20	2
52	26-Mar-20	2
53	26-Mar-20	2
54	26-Mar-20	2
55	26-Mar-20	2
56	26-Mar-20	2
57	2-Apr-20	9
58	7-Apr-20	14
59	13-Apr-20	20
60	13-Apr-20	20
61	14-Apr-20	21
62	23-Apr-20	30
63	23-Apr-20	30
64	23-Apr-20	30
65	23-Apr-20	30
66	24-Apr-20	31
67	27-Apr-20	34
68	27-Apr-20	34
69	27-Apr-20	34
70	4-May-20	41
71	5-May-20	42
72	13-Apr-20	20
73	15-May-20	52
74	17-May-20	54
75	18-May-20	55
76	20-May-20	57
77	21-May-20	58
78	21-May-20	58
79	28-May-20	65
80	9-Jun-20	77
81	9-Jun-20	77
82	10-Jun-20	78
83	11-Jun-20	79
84	14-Jun-20	82
85	15-Jun-20	83
86	15-Jun-20	83
87	18-Jun-20	86
88	30-Jun-20	98
89	30-Jun-20	98
90	3-Jul-20	101
91	7-Jul-20	105
92	13-Jul-20	111
93	14-Jul-20	112
94	21-Jul-20	119
95	23-Jul-20	121
96	24-Jul-20	122
97	24-Jul-20	122
98	24-Jul-20	122
99	31-Jul-20	129
100	7-Aug-20	136
101	7-Aug-20	136
102	17-Aug-20	146
103	21-Aug-20	150
104	24-Aug-20	153
105	27-Aug-20	156
106	28-Aug-20	157
107	31-Aug-20	160
108	1-Sep-20	161
109	8-Sep-20	168
110	8-Sep-20	168
111	8-Sep-20	168
112	8-Sep-20	168
113	9-Sep-20	169
114	11-Sep-20	171
115	19-Sep-20	179
116	30-Sep-20	190
117	1-Oct-20	191
118	1-Oct-20	191
119	1-Oct-20	191
120	1-Oct-20	191
121	1-Oct-20	191
122	8-Oct-20	198
123	9-Oct-20	199
124	16-Oct-20	206
125	21-Oct-20	211
126	21-Oct-20	211
127	26-Oct-20	216
128	26-Oct-20	216
129	27-Oct-20	217
130	3-Nov-20	224
131	3-Nov-20	224
132	6-Nov-20	227
133	9-Nov-20	230
134	13-Nov-20	234
135	19-Nov-20	240
136	20-Nov-20	241
137	22-Nov-20	243
138	26-Nov-20	247
139	26-Nov-20	247
140	26-Nov-20	247
141	30-Nov-20	251
142	12-Dec-20	263
143	22-Dec-20	273
144	25-Dec-20	276
145	25-Dec-20	276
146	29-Dec-20	280
147	30-Dec-20	281
148	8-Jan-21	290
149	15-Jan-21	297
150	15-Jan-21	297
151	15-Jan-21	297
152	22-Jan-21	304
153	26-Jan-21	308
154	26-Jan-21	308
155	9-Feb-21	322
156	9-Feb-21	322
157	11-Feb-21	324
158	11-Feb-21	324
159	12-Feb-21	325
160	16-Feb-21	329
161	16-Feb-21	329
162	17-Feb-21	330
163	18-Feb-21	331
164	18-Feb-21	331
165	23-Feb-21	336
166	23-Feb-21	336
167	23-Feb-21	336
168	2-Mar-21	343
169	9-Mar-21	350
170	29-Dec-2020
171	Available
172	Available
173	Available
174	Available
175	Available
176	Available
177	Available
178	Available
179	Available
180	Available
181	Available
182	Available
183	Available
184	Available
185	Available
186	Available
187	Available
188	Available
189	Available
190	Available
191	Available
192	Available
193	Available
194	Available
195	Available
196	Available
197	Available
198	Available
199	Available
200	Available
201	Available
202	Available
203	Available
204	Available
205	Available
206	Available
207	Available
208	Available
209	Available
210	Available
211	Available
212	Available
213	Available
214	Available
215	Available
216	Available
217	Available
218	Available
219	Available
220	Available
221	NO
222	NO
223	NO
224	NO
225	NO
226	NO
227	NO
228	NO
229	NO
230	NO
231	NO
232	NO
233	NO
234	NO
235	NO
236	NO
Sheet1

 

[aliaslamy2k]

Hi Peter,

Actually i am trying to get the vehicle license plate number which falls under the criteria of 30 - 45 days, 10 to 30 days and 0 to 10 days.
Yes, i have entered these values manually because i dont know how to write a formula to get this resuls.

Excel Formulas.xlsx
	AO	AP	AQ	AR	AS	AT	AU	AV
6	30 - 45			10 - 30			0 - 10
7	Vehicle Number	LIC Days Remaining		Vehicle Number	LIC Days Remaining		Vehicle Number	LIC Days Remaining
8	240767	30		264881	14		320055	1
9	283597	30		104516	20		455101	2
10	314852	30		305660	20		216830	2
11	312209	30		127539	21		215797	2
12	253943	31		718771	20		214218	2
13	46242	34					306796	2
14	320296	34					305114	2
15	320295	34					556492	2
16	168467	41					307242	2
17	34163	42					306924	2
18							307524	2
19							306558	2
20							305093	2
21							305194	2
22							305455	2
23							305712	2
24							305196	2
25							307119	2
26							206756	2
27							306415	2
28							263443	9
Sheet1

 

[Peter_SSs]

See if this is what you want. If so, hopefully you can adapt to your actual ranges and for the 10-30 and 0-10 sections.

aliaslamy2k 2020-03-27 1.xlsm
	D	E	F	G	H	I	J
5	ID	Vehicle Number	LIC Days Remaining
6	1	7823	128			30 - 45
7	2	54705	145			Vehicle Number	LIC Days Remaining
8	3	34346	200			305455	30
9	4	116609	77			41207	31
10	5	570242	29			320055	31
11	6	633595	103			718771	33
12	7	15305	41			215797	37
13	8	140236	71			15305	41
14	9	15257	62			283780	42
15	10	49148	3
16	11	232690	25
17	12	23298	86
18	13	41204	68
19	14	42990	21
20	15	186324	127
21	16	41205	110
22	17	41207	31
23	18	41208	187
24	19	29555	86
25	20	46963	120
26	21	46962	54
27	22	283779	122
28	23	283780	42
29	24	254273	17
30	25	739050	153
31	26	668344	180
32	27	594941	90
33	28	460470	2
34	29	264925	142
35	30	283761	145
36	31	283760	65
37	32	320055	31
38	33	455101	186
39	34	216830	70
40	35	215797	37
41	36	214218	192
42	37	306796	127
43	38	305114	114
44	39	556492	69
45	40	307242	20
46	41	306924	1
47	42	307524	95
48	43	306558	47
49	44	305093	55
50	45	305194	163
51	46	305455	30
52	47	305712	143
53	48	305196	191
54	49	307119	69
55	50	206756	63
56	51	306415	170
57	52	263443	155
58	53	264881	198
59	54	104516	125
60	55	305660	126
61	56	127539	22
62	57	240767	49
63	58	283597	132
64	59	314852	153
65	60	312209	125
66	61	253943	15
67	62	46242	89
68	63	320296	143
69	64	320295	128
70	65	168467	185
71	66	34163	48
72	67	718771	33
73	68	266580	93
74	69	211949	26
75	70	159021	197
Sheet1

Cell Formulas
Range	Formula
I8:I17	I8	=IF(J8="","",INDEX(E$6:E$75,AGGREGATE(15,6,(ROW(F$6:F$75)-ROW(F$6)+1)/(F$6:F$75=J8),COUNTIF(J$8:J8,J8))))
J8:J17	J8	=IFERROR(AGGREGATE(15,6,F$6:F$75/((F$6:F$75>=30)*(F$6:F$75<45)),ROWS(J$8:J8)),"")