Ask Your Question
0

How do I find the first and last matches in a dataset? [closed]

asked 2014-03-23 21:22:41 +0200

M_Bison gravatar image

Suppose I have the following data set...
_   A  B
1   1  9
2   2  10
3   3  11
4   2  11
5   3  12
6   3  13

(Column A is the team number and column B is the age of the kid). Suppose I want to know the youngest kid on team 2. I figured out the first match. I can say,
=INDEX(B$1:B$5,MATCH(2,A$1:A$5,0),1)
...and it would tell me that the youngest kid on team 2 is 10yrs old. How would I find the oldest member of a team? I want to say, how old is the oldest kid on team 2 and have it respond "11", or how old is the oldest kid on team 3 and have it respond "13".

Thanks!

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 08:47:32.816952

1 Answer

Sort by » oldest newest most voted
0

answered 2014-03-23 22:46:52 +0200

m.a.riosv gravatar image

I think an array formula like this must work: {=MAX((B$1:B$6=2)*C$1:C$6)} enter the formula without the brackets and use Ctrl+Enter not only enter.

To avoid the array expression you can use a formula like: =SUMPRODUCT(MAX((B$1:B$6=2)*C$1:C$6))

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-03-23 21:22:41 +0200

Seen: 436 times

Last updated: Mar 23 '14