Lookup Multiple Criteria

[build]

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

 

[Teethless mama]

Use INDEX and MATCH functions instead of VLOOKUP function

 

[Max]

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$6=F1,B$1:B$6)),(IF(D$1$6=F1,B$1:B$6)),0))
Copy G1 down to return the required results. Adapt the ranges to suit.

 

[JW]

Since they are sorted, you can use a Index/Match formula. An Index/
Match will basically do the exact same thing as a vLookup, but it will
allow you to look to the left.
Assuming your team is in column D and your number is in column B:
=INDEX(B1:B7,MATCH("blue",D17,0))

 

[build]

THANK YOU all very much.

Since they are sorted, you can use a Index/Match formula. An Index/
Match will basically do the exact same thing as a vLookup, but it will
allow you to look to the left.
Assuming your team is in column D and your number is in column B:
=INDEX(B1:B7,MATCH("blue",D17,0))