I have a table as shown in the example below. Can you help me with a
formula that pulls out the the project code with maximum hours for an
employee? This
table is from a pivot,

Employee ID Project Number Hours
78904 2057677 140
100 IU 8
530 IU 12
78904 Total 160

87659 2064777 120
2072147 40
87659Total 160

The result should show me like:

Employee ID Project Number Hours
78904 2057677 140
87659 2064777 120

CAn anyone help? Thanks!

Assuming the table data is in A1:C10 with Row1 as headers please use the
below formula. Col B is project numbers and ColC is hours

=INDEX(B2:B10,MATCH(MAX(C2:C10),C2:C10,0))
--
If this post helps click Yes
---------------
Jacob Skaria


"VM" wrote:

I have a table as shown in the example below. Can you help me with a
formula that pulls out the the project code with maximum hours for an
employee? This
table is from a pivot,

Employee ID Project Number Hours
78904 2057677 140
100 IU 8
530 IU 12
78904 Total 160

87659 2064777 120
2072147 40
87659Total 160

The result should show me like:

Employee ID Project Number Hours
78904 2057677 140
87659 2064777 120

CAn anyone help? Thanks!

On Apr 28, 12:02*pm, Jacob Skaria
wrote:
Assuming the table data is in A1:C10 with Row1 as headers please use the
below formula. Col B is project numbers and ColC is hours

=INDEX(B2:B10,MATCH(MAX(C2:C10),C2:C10,0))
--
If this post helps click Yes
---------------
Jacob Skaria



"VM" wrote:
I have a table as shown in the example below. Can you help me with a
formula that pulls out the the project code with maximum hours for an
employee? This
table is from a pivot,

Employee ID * * Project Number *Hours
78904 * * * * * * * 2057677 * * * * * *140
* * * * * * * * * * * * 100 IU * * * * * * * *8
* * * * * * * * * * * * 530 IU * * * * * * * *12
78904 Total * * * * * * * * * * * * * * *160

87659 * * * * * * * 2064777 * * * * * 120
* * * * * * * * * * * *2072147 * * * * * *40
87659Total * * * * * * * * * * * * * * * 160

The result should show me like:

Employee ID * * Project Number *Hours
78904 * * * * * * * 2057677 * * * * * *140
87659 * * * * * * * 2064777 * * * * * 120

CAn anyone help? Thanks!- Hide quoted text -

- Show quoted text -

Thanks Jacob. I tried the formula, but it shows me the value of 0..I'm
not sure if I'm doing something wrong?

If you are having your data in sequence...I mean with no blanks like below
you can use the below formula to get the maximum hours based on employee ID
and project name...

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in the Formula Bar you can notice the curly braces at both ends
"{=<formula}"

=MAX(IF($A$2:$A$10="EmpID",IF($B$2:$B$10="Project" ,$C$2:$C$10)))

Suppose you have the data in this format from A2 to C10. Row 1 is having
headers.

Employee ID Project Hours
Emp1 ProA 9
Emp1 ProA 8
Emp1 ProA 7
Emp1 ProB 1
Emp2 ProB 2
Emp1 ProB 3
Emp2 ProC 10
Emp2 ProA 1
Emp2 ProC 30




If this post helps click Yes
---------------
Jacob Skaria


"VM" wrote:

On Apr 28, 12:02 pm, Jacob Skaria
wrote:
Assuming the table data is in A1:C10 with Row1 as headers please use the
below formula. Col B is project numbers and ColC is hours

=INDEX(B2:B10,MATCH(MAX(C2:C10),C2:C10,0))
--
If this post helps click Yes
---------------
Jacob Skaria



"VM" wrote:
I have a table as shown in the example below. Can you help me with a
formula that pulls out the the project code with maximum hours for an
employee? This
table is from a pivot,

Employee ID Project Number Hours
78904 2057677 140
100 IU 8
530 IU 12
78904 Total 160

87659 2064777 120
2072147 40
87659Total 160

The result should show me like:

Employee ID Project Number Hours
78904 2057677 140
87659 2064777 120

CAn anyone help? Thanks!- Hide quoted text -

- Show quoted text -

Thanks Jacob. I tried the formula, but it shows me the value of 0..I'm
not sure if I'm doing something wrong?