Excel 2007 : Index, Match, Large Formula: Multiple Criteria, Multiple Ranges

**SimpleJack** · 08-15-2011, 10:49 AM

Greetings everyone. I am a new member here to the forum, and I have never had to post a question because I could always find the answer. You guys are great.

I have a workbook with 4 worksheets. Summary sheet, and 3 sheets of data. The data sheets contain a part number, part color, part price, and month. (Example attached)

The worksheets have slightly different ranges. I want to search the 3 worksheets and return the 5 largest and 5 smallest values that meet 2 criteria that I specify. (Color and Month)

I think an INDEX, MATCH, LARGE combination can do this, but I don't know how to combine ranges and criteria in an index formula of this type. I have attached a simple example, and I hope that you can help me.

**NBVC** · 08-15-2011, 11:15 AM

The best way would be to put all three tables in one sheet set one below the other, then you have one table to deal with...

If you must keep it as is and you will not have duplicate prices for same combination of January and Red, and you only have the 3 tables, then try this...

In E6:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.

In C6:

Please Login or Register  to view this content.

confirmed with just ENTER and copied down

similary for smallest, in E16

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.

and in C16:

Please Login or Register  to view this content.

copied down

**SimpleJack** · 08-15-2011, 12:07 PM

I appreciate your quick response.

The formula doesn't quite grab the data in the correct order, and it appears to have skipped a few values in the largest and smallest ranking.

Thanks for the help, let me know if I did something wrong...

-Chris

**NBVC** · 08-15-2011, 12:14 PM

Yes, you are right... I messed that one up... I was taking largest from each table and getting max of those, then second largest from each and getting max of each, etc... which is wrong.

I revert back to my original suggestion to tabulate it all into one large table first, then find the largest/smallest from that table.

otherwise, it would be complicated, unless somebody can give you a VBA solution.

**dr3amz** · 07-19-2013, 07:33 AM

yeah i can do with tables etc - but i want to do it in one formula

(perhaps im trying to break excel)

sumproduct kinda worked, but on 13k lines cripples excel

**arlu1201** · 07-19-2013, 08:54 AM

Dr3amz,

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.