+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    08-15-2011
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    7

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    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
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    08-15-2011
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Index, Match, Large Formula: Multiple Criteria, Multiple Ranges

    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
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    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. #5
    Registered User
    Join Date
    07-19-2013
    Location
    Southampton, England
    MS-Off Ver
    Excel 2010
    Posts
    12

    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. #6
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    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.

    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.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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