INDEX MATCH MAX Query

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

If you post a sample (10-20 rows) you can change the skus to A, B, C, D....

An array formula in D3 (similar in D4):
=IF($C$2="All Categories",MAX($E$8:$E$18),MAX(IF($B$8:$B$18=$C$2,$E$8:$E$18)))

An ordinary formula in C3 (similar in C4):
=INDEX($C$8:$C$18,MATCH(D3,$E$8:$E$18,0))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

I changed the DV for Soft Drinks to Soft Drinks/Juices. They must match.

In the attached file, I added an error trap to account for missing values...

I meant to add.... is there a significant chance of a tied result? This would require some changes.

Otherwise....You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.