Two Criteria Index Match on Multiple Sheets with Multiple Values

Hi everyone!
I'm not too terrible at excel, but this question has me stumped. I found a similar post but couldn't get the formulas to work in my example. I've been trying for about a day now :-)

I have a document with a Summary page as well as three additional tabs that each include sales for a specific product (Product1, Product2, and Product3).

On the summary tab, I would like the user to be able to select from 2 different drop down boxes -- one for customer owner and one for product name. Based upon those choices, I would like the file to return all of the corresponding customer names and total sales. For example, if Joe Smith and Product 1 is selected, I would like to see the names of all of the customers and total sales for everyone owned by Joe Smith on the Product 1 tab. The sample I attached is just a simpler example of my actual file. I would ideally like to bring back up to 50 records, ideally sorted by total sales (bringing back to top 50 total sales).

I know I couldn't probably build a pivot table for something like this, but I really need it in list form.

Also, if the selections would produce no results, is there a way for the cells to stay blank or show a 0 instead of showing an NA error.

I'm hoping someone can help! I would GREATLY appreciate it!!

Thank you!!!

Megg

I haven't the slightest idea how that is working, but IT WORKS! Thank you, thank you, thank you!! :-) I just have one question...in my actual document, my Product sheets will have varying number of rows (in the example, I provided exactly 8 rows in each sheet). Will it work to change the formulas to b:b, a:a, etc. instead of using specific cells?

Great! Thanks again for all of your help!

I'm so sorry, but I am actually experiencing a problem with this formula, and I don't understand enough about the formula to try to correct it. I have been trying for quite some time. Although the formula is generally working, I am running into a problem with incorrect names coming through. I have found the reason, but I do not know how to correct it. It looks like when the sales dollar amounts are the same for multiple customers, the formula is just returning the first person in the list with that sales amount, not necessarily matching to the customer owner. I'm guessing this is caused by the sort. I am okay taking the sort piece out, as long as I am getting the correct customer names on the list. Any suggestions? Thank you!!

Hi

To overcome the duplicate dollar values for multiple customers, you'll see that I've inserted a helper column (column D) in the second to fourth tabs in your file (attached). I've also slightly tweaked the OP's formulas in the 'Summary' tab, so that all previous references to the cell ranges in column C applicable to the worksheet in question now refers to those in the helper column instead.



Hope this helps.

P.

Works perfectly! Can't thank you enough!! :-)