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?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,296
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
54,296
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,878
Messages
5,834,200
Members
430,263
Latest member
abz54

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