# Thread: Validation for date required with lowest number Thanks: 0 Likes: 0

1. ## Validation for date required with lowest number

Hi Team,

I have below data, looking for a formula in column F to see which is the lowest number in column E for column A. Expected result provided in column F.

 Column A Column B Column C Column D Column E Column F Number name date todays date today date - date Result 17137 BD 04/07/2019 30/07/2019 26 TRUE 17137 BR 17/05/2019 30/07/2019 74 17137 EP 04/07/2019 30/07/2019 26 TRUE 17137 LD 04/07/2019 30/07/2019 26 TRUE 17137 PA 14/06/2019 30/07/2019 46 17137 TS 20/05/2019 30/07/2019 71 18370 BB 20/05/2019 30/07/2019 71 18370 BR 20/05/2019 30/07/2019 71 18370 EP 20/05/2019 30/07/2019 71 18370 LD 20/05/2019 30/07/2019 71 18370 PA 19/06/2019 30/07/2019 41 18370 TS 29/05/2019 30/07/2019 62 TRUE

2. ## Re: Validation for date required with lowest number - Urgent

try

=IF(E3=MINIFS(\$E\$3:\$E\$14,\$A\$3:\$A\$14,A3),TRUE,"")

3. ## Re: Validation for date required with lowest number - Urgent

Solution using MAXIFS in column F
Alternative using AGGREGATE in column H

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
J
1
Number name date todays date today date - date Result
(MAXIFS)
formula in F2 copied down Result
(AGGREGATE)
formula in F2 copied down
2
17137
BD
04/07/2019
30/07/2019
26
TRUE
=MINIFS(E:E,A:A,A2)=E2
TRUE
=AGGREGATE(15,6,E:E/(A:A=A2),1)=E2
3
17137
BR
17/05/2019
30/07/2019
74
FALSE
FALSE
4
17137
EP
04/07/2019
30/07/2019
26
TRUE
TRUE
5
17137
LD
04/07/2019
30/07/2019
26
TRUE
TRUE
6
17137
PA
14/06/2019
30/07/2019
46
FALSE
FALSE
7
17137
TS
20/05/2019
30/07/2019
71
FALSE
FALSE
8
18370
BB
20/05/2019
30/07/2019
71
FALSE
FALSE
9
18370
BR
20/05/2019
30/07/2019
71
FALSE
FALSE
10
18370
EP
20/05/2019
30/07/2019
71
FALSE
FALSE
11
18370
LD
20/05/2019
30/07/2019
71
FALSE
FALSE
12
18370
PA
19/06/2019
30/07/2019
41
TRUE
TRUE
13
18370
TS
29/05/2019
30/07/2019
62
FALSE
FALSE
14
 Sheet: Sheet4

4. ## Re: Validation for date required with lowest number - Urgent

.. and another one that should work in all versions.

Minimums

 A B C D E F 1 Number name date todays date today date - date Result 2 17137 BD 4/07/2019 30/07/2019 26 TRUE 3 17137 BR 17/05/2019 30/07/2019 74 4 17137 EP 4/07/2019 30/07/2019 26 TRUE 5 17137 LD 4/07/2019 30/07/2019 26 TRUE 6 17137 PA 14/06/2019 30/07/2019 46 7 17137 TS 20/05/2019 30/07/2019 71 8 18370 BB 20/05/2019 30/07/2019 71 9 18370 BR 20/05/2019 30/07/2019 71 10 18370 EP 20/05/2019 30/07/2019 71 11 18370 LD 20/05/2019 30/07/2019 71 12 18370 PA 19/06/2019 30/07/2019 41 TRUE 13 18370 TS 29/05/2019 30/07/2019 62

 Cell Formula F2 =IF(SUMPRODUCT(--(A\$2:A\$13=A2),--(E\$2:E\$13),"",TRUE)

Excel tables to the web >> Excel Jeanie HTML 4

5. ## Re: Validation for date required with lowest number - Urgent

Hi Peter,

Formula isnt working it says - The formula is missing an opening or closing parentthesis

6. ## Re: Validation for date required with lowest number - Urgent

Originally Posted by exceluser9
Hi Peter,

Formula isnt working it says - The formula is missing an opening or closing parentthesis
My mistake - the forum often cuts off formulas that contain an < sign. That F2 formula should have been

=IF(SUMPRODUCT(--(A\$2:A\$13=A2),--(E\$2:E\$13<E2)),"",TRUE)

7. ## Re: Validation for date required with lowest number - Urgent

Its working, thank you Peter

8. ## Re: Validation for date required with lowest number - Urgent

Originally Posted by exceluser9
Its working, thank you Peter
You're welcome.