Lookup

[philcart]

hi,
I have a list of Christian names in column in a worksheet, eg John, Richard, Peter etc. In another sheet I have a list of names in colA, their race times in colB. There are many duplicate names in colA (sorted in time order), so what I need is a way of looking up the fastest of each in turn form the list on the previous sheet - ie the fastest John, then the fastest Richard, then the fastest Peter, and so on, and put "Winner" in the cell in colC.
Hope someone can help.
Phil

 

[Brian from Maui]

Have a look at this,

http://www.mrexcel.com/board2/viewtopic.php?p=224930&highlight=rank#224930

 

[Barry Katcher]

Before going any further, are you saying that each person gets 2 or more heats, and each person's fastest time from those heats is compared the other people's fastest time, and that gives the winner?

Please be a bit more specific in what your data looks like and what you would like to achieve.

 

[Aladin Akyurek]

I assume one with the smallest time is the winner...

The data...

Book11
	A	B	C	D
1	Name	Time
2	Richard	10
3	John	12
4	Bob	12
5	Bob	12
6	Peter	13
7	Bob	13
8	John	14
9	John	14
10	Peter	14
11	Richard	15
12	Richard	15
13	Peter	16
14
Sheet2

The processing...

Book11
	A	B	C	D
1	Name	FastestTime	Rank	Winner
2	John	12	2	Richard
3	Richard	10	1
4	Peter	13	4
5	Bob	12	3
Sheet1

Formulas...

B2:

=MIN(IF(Sheet2!$A$2:$A$13=A2,Sheet2!$B$2:$B$13))

which must be entered using control+shift+enter, not just enter.

C2:

=RANK(B2,$B$2:$B$5,1)+COUNTIF($B$2:B2,B2)-1

D2:

=INDEX($A$2:$A$5,MATCH(1,$C$2:$C$5,0))

Note that there might be more than one winner. The above formula will just show one of them.

Is this what you are looking for?

 

[philcart]

:D To all who replied so pomptly - thank you very much. i will look at all your solutions. Barry - I will give more details if the other suggestions don't work out.
Cheers,
Phil