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