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

1. ## Index, Match, Large Formula: Multiple Criteria, Multiple Ranges

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.

2. ## Re: Index, Match, Large Formula: Multiple Criteria, Multiple Ranges

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

3. ## Re: Index, Match, Large Formula: Multiple Criteria, Multiple Ranges

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

4. ## Re: Index, Match, Large Formula: Multiple Criteria, Multiple Ranges

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.

5. ## Re: Excel 2007 : Index, Match, Large Formula: Multiple Criteria, Multiple Ranges

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

6. ## Re: Excel 2007 : Index, Match, Large Formula: Multiple Criteria, Multiple Ranges

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.

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1