Formula for Classification for best time in a race

[GerryZ]

Hello al excel guru
I need a formula in D14 and copy down
that gives me back the ID numbers with 3 criteria:
1) Gender
2) CAT
3) best time

Book1
	A	B	C	D
1	ID number	time	gender	CAT.
2	1	0:00:05	M	A
3	35	0:00:10	F	A
4	2	0:00:19	M	B
5	15	0:00:26	F	B
6	100	0:00:29	M	A
7	5	0:01:13	F	B
8	12	0:01:15	F	A
9	16	0:01:18	F	B
10	44	0:01:20	M	A
11
12
13	citeria GENDER	M	time	ID number
14	criteria CAT.	A	00:01:20	?
15			00:01:18	?
16			00:01:15	?
17			00:01:13	?
18			00:00:29	?
19			00:00:26	?
20			00:00:19	?
21			00:00:10	?
22			00:00:05	?
Foglio1

 

[Jake Blackmore]

Hi,

Ok so using the time in column C I make the formula an array formula (which means rather than just hitting enter after typing it in you need to hold down CTRL & SHIFT & then click ENTER)
D14=MAX($A$2:$A$10*(--($C$2:$C$10=$B$13)*--($D$2:$D$10=$B$14)*--($B$2:$B$10=C14)))

If there isn't an answer it will simply return zero - if you don't enter this as an array you will probably get a #Value error.
Jake

 

[GerryZ]

Wooow!! it works fine
thank you so much!!

 

[Jake Blackmore]

Welcome