# Validation for date required with lowest number

#### exceluser9

##### Active Member
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

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### AlanY

##### Well-known Member
Re: Validation for date required with lowest number - Urgent

try

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

#### Yongle

##### Well-known Member
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
Numbernamedatetodays datetoday date - dateResult
(MAXIFS)
formula in F2 copied downResult
(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

#### Peter_SSs

##### MrExcel MVP, Moderator
Re: Validation for date required with lowest number - Urgent

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

Excel Workbook
ABCDEF
1Numbernamedatetodays datetoday date - dateResult
217137BD4/07/201930/07/201926TRUE
317137BR17/05/201930/07/201974
417137EP4/07/201930/07/201926TRUE
517137LD4/07/201930/07/201926TRUE
617137PA14/06/201930/07/201946
717137TS20/05/201930/07/201971
818370BB20/05/201930/07/201971
918370BR20/05/201930/07/201971
1018370EP20/05/201930/07/201971
1118370LD20/05/201930/07/201971
1218370PA19/06/201930/07/201941TRUE
1318370TS29/05/201930/07/201962
Minimums

Last edited:

#### exceluser9

##### Active Member
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

#### Peter_SSs

##### MrExcel MVP, Moderator
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
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)

#### exceluser9

##### Active Member
Re: Validation for date required with lowest number - Urgent

Its working, thank you Peter

#### Peter_SSs

##### MrExcel MVP, Moderator
Re: Validation for date required with lowest number - Urgent

Its working, thank you Peter
You're welcome.

1,089,597
Messages
5,409,194
Members
403,256
Latest member
Viq