INDEX + MATCH + MAX Question

stupideye

New Member
Joined
Aug 6, 2019
Messages
14
Hello,

I am doing analysis on products and seeing what the best performing week was for them. I'm doing this for the year 2018 and 2019.

On the "Best Week" sheet, which is a Pivot Table (it pulls from a data tab with the raw sales info):
Product #'s are listed in column B.
Years are listed in row 5.
Weeks are listed in row 6.
C7:CN7970 currently have the $ amounts. More will be added week-to-week.

On the "Products" sheet:
Product #'s are listed in column B - and will be shifted around as filters are applied for looking at top revenue drivers, etc. so a static formula will not work.
I have a title for "Best 2018 Week" which needs to find the best week (highest $) by item - so we know what week was the best.
Another title is "Best 2018 Week Rev" which will look at that "Best 2018 Week" and display the revenue that product earned.

Here is a image of what I'm talking about: https://i.ibb.co/jHK3Ht6/Capture.png

I greatly appreciate any assistance. Thank you in advance!
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Watch MrExcel Video

Forum statistics

Threads
1,122,734
Messages
5,597,809
Members
414,177
Latest member
RezaAkhund

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