Large Function + Search Criteria + Ignore Duplicate values

[Aldonin]

Hi all gurus:

Quick question here, I have a data table like the one below. For this, id like to be able to pull the result in column "C" this is, the second highest time for each specific code only. The challenge I've had so far is that Large does not account for duplicate values, so for Code 2000 I will always get 65 as the 2nd highest time.

Any suggestions?

Thanks all!!

[TABLE="width: 276"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Code
[/TD]
[TD]Time
[/TD]
[TD]Personal Second Best
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2000
[/TD]
[TD]65
[/TD]
[TD]47
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2000
[/TD]
[TD]65
[/TD]
[TD]47
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2000
[/TD]
[TD]47
[/TD]
[TD]47
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]2000
[/TD]
[TD]65
[/TD]
[TD]47
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]2000
[/TD]
[TD]33
[/TD]
[TD]47
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]2000
[/TD]
[TD]65
[/TD]
[TD]47
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]2001
[/TD]
[TD]33
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]2001
[/TD]
[TD]33
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]2001
[/TD]
[TD]12
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

One possibility but I'm sure there's a more efficient way:

Book1
	A	B	C
1	code	time	psb
2	2000	65	47
3	2000	65	47
4	2000	47	47
5	2000	65	47
6	2000	33	47
7	2000	65	47
8	2001	33	12
9	2001	33	12
10	2001	12	12
Sheet1

Cell Formulas
Range	Formula
C2	{=MAX(IF($A$2:$A$10=$A2,IF($B$2:$B$10<>MAX(IF($A$2:$A$10=$A2,$B$2:$B$10)),$B$2:$B$10)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD