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

Use INDEX and MATCH functions instead of VLOOKUP function


"build" wrote:

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

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

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",D1:D7,0))
build wrote:
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

THANK YOU all very much.


"JW" wrote in message
oups.com...
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",D1:D7,0))
build wrote:
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