Matching max value to project number

Claymationator

Well-known Member
Joined
Sep 26, 2006
Messages
705
I have a project number listed in column A2:A155, dates by week listed from B1:AE1, and then the number of hours spent on a specific project for each week in each column. In B158:AE158 I have a max formula that returns the highest number of hours for each week, and what I need to do is find the corresponding project number in column A (that matches the max hours), and return that below each max for each week (in B159:AE159).

Any quick formulas that will do the trick?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
I have a project number listed in column A2:A155, dates by week listed from B1:AE1, and then the number of hours spent on a specific project for each week in each column. In B158:AE158 I have a max formula that returns the highest number of hours for each week, and what I need to do is find the corresponding project number in column A (that matches the max hours), and return that below each max for each week (in B159:AE159).

Any quick formulas that will do the trick?
What if there is more than one equal maximum value in a week?
 

Claymationator

Well-known Member
Joined
Sep 26, 2006
Messages
705
If there are duplicates, then I just need it to return the first max value in the list.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
If there are duplicates, then I just need it to return the first max value in the list.
Then try this. Formulas copied across.

Excel Workbook
ABCDEFGH
1Proj01/01/200808/01/200815/01/200822/01/200829/01/200805/02/2008
21234
32125212
4340061
54005301
65222222
7Max435622
8Proj Max312352
9
Projects
 

Watch MrExcel Video

Forum statistics

Threads
1,122,691
Messages
5,597,562
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top