Source data is assumed in cols B to D, from row1 down to row6
with col B = scores,, col C = players, col D = Teams

List the teams in F1 down, ie: Red, Blue Green
Place in G1, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=INDEX(C$1:C$6,MATCH(MIN(IF(D$1:D$6=F1,B$1:B$6)),( IF(D$1:D$6=F1,B$1:B$6)),0))
Copy G1 down to return the required results. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"build" wrote in message
...
G'day All,
I have a table of scores, player numbers, players, teams.
I want to lookup the lowest scoring player from each team using the same
formula.
i.e.(in csv format)
1,6,John,Red
2,4,Pete,Blue
3,2,Nick,Red
4,9,Andy,Green
5,3,Bruce,Blue
6,5,Frank,Green

In the example I would want to return 4 for Blue team.
The scores are in asending order, so the first occurance of the
lookup_value will return the lowest score.
I would have used vlookup but the look up value is on the right not left
of the table_array.

Thanking you in anticipation,
build