Issue trying to identify earliest date & time

[sharky12345]

I'm trying to identify the earliest date & time with a single criteria and what's returned is incorrect.

The formula I'm using is this:

=MIN(IF($A$2:$A5053=D2,$B$2:$B5053))

It's producing "00/01/1900 00:00"

The relevant cells are formatted as custom 'dd/mm/yyyy hh:mm' but no matter how I format the cells, the result is always incorrect.

I've attached a sample of the workbook, if anyone can help me understand how to resolve this please?

MrExcel.xlsx
	A	B	C	D
1	Index Number	Index Time	Index Number	Earliest Index Time
2	IND2300009260	20/04/2023 09:19	IND2300009260	00/01/1900 00:00
3	IND2300009260	11/04/2024 12:27	IND2300011569
4	IND2300011569	16/05/2023 14:50	IND2300013377
5	IND2300011569	10/04/2024 00:27	IND2300016533
6	IND2300011569	10/04/2024 00:29	IND2300017703
7	IND2300013377	06/06/2023 01:09	IND2300025545
8	IND2300013377	29/04/2024 13:09	IND2400000003
9	IND2300016533	11/07/2023 02:10	IND2400001139
10	IND2300016533	11/07/2023 20:30	IND2400001938
11	IND2300016533	10/01/2024 15:37	IND2400002279
12	IND2300016533	10/04/2024 16:17	IND2400002425
13	IND2300016533	10/04/2024 23:51	IND2400002503
14	IND2300017703	23/07/2023 16:47	IND2400007799
15	IND2300017703	23/07/2023 17:19	IND2400008564
16	IND2300017703	23/07/2023 23:29	IND2400010159
17	IND2300017703	09/04/2024 16:12	IND2400010908
18	IND2300017703	10/04/2024 00:21	IND2400011060
19	IND2300025545	20/10/2023 03:02	IND2400011062
20	IND2300025545	20/10/2023 03:24	IND2400011063
21	IND2300025545	20/10/2023 14:22	IND2400011069
22	IND2300025545	14/04/2024 11:49	IND2400011081
23	IND2400000003	01/01/2024 01:20	IND2400011082
24	IND2400000003	01/01/2024 04:54	IND2400011084
25	IND2400000003	01/04/2024 12:00	IND2400011085
26	IND2400001139	13/01/2024 00:49	IND2400011087
27	IND2400001139	12/04/2024 18:17	IND2400011094
28	IND2400001938	22/01/2024 12:13	IND2400011096
29	IND2400001938	19/04/2024 19:57	IND2400011097
30	IND2400002279	25/01/2024 21:25	IND2400011100
31	IND2400002279	08/04/2024 16:34	IND2400011101
32	IND2400002425	27/01/2024 18:29	IND2400011102
33	IND2400002425	24/04/2024 13:46	IND2400011104
34	IND2400002503	28/01/2024 13:08	IND2400011105
35	IND2400002503	28/01/2024 17:00	IND2400011106
36	IND2400002503	02/04/2024 16:50	IND2400011107
37	IND2400007799	14/03/2024 19:55	IND2400011108
38	IND2400007799	03/04/2024 10:51	IND2400011111
39	IND2400008564	08/04/2024 12:01	IND2400011112
40	IND2400010159	27/03/2024 11:47	IND2400011117
41	IND2400010159	27/03/2024 12:39	IND2400011119
42	IND2400010159	05/04/2024 11:58	IND2400011122
43	IND2400010908	31/03/2024 05:40	IND2400011124
MREXCEL

Cell Formulas
Range	Formula
D2	D2	=MIN(IF($A$2:$A5053=C2,$B$2:$B5053))

 

[sharky12345]

With 2016 you will need to confirm your formula with Ctrl Shift Enter, rather than just Enter.

Still producing zero Fluff

 

[MrRajKumar]

You can use AGGREGATE function.

=AGGREGATE(15,6,$B$2:$B5053/($A$2:$A5053=C2)/($B$2:$B5053<>""),1)

Then warp up with IFERROR to avoid error.

 

[sharky12345]

You can use AGGREGATE function.

=AGGREGATE(15,6,$B$2:$B5053/($A$2:$A5053=C2)/($B$2:$B5053<>""),1)

Then warp up with IFERROR to avoid error.

Thanks - but that's producing 20/04/2023 09:19 which isn't the correct result

 

[Fluff]

That's the correct result with the data you posted.

 

[sharky12345]

Of course, doh!

I forgot the change of year!

I'll check the other results and report back.....

 

[sharky12345]

The aggregate solution works guys - thanks to you all for taking the time to reply