+ Reply to Thread
Results 1 to 12 of 12

Increase criteria for {=INDEX(A:A,MATCH(LARGE(IF(B:B=52,C:C),1),C:C,0))}

  1. #1
    Registered User
    Join Date
    08-14-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    10

    Increase criteria for {=INDEX(A:A,MATCH(LARGE(IF(B:B=52,C:C),1),C:C,0))}

    Hello,

    I'm currently using the following array formulas to return top 10 products with the highest sales for a certain week (such as week 52).

    {=INDEX(A:A,MATCH(LARGE(IF(B:B=52,C:C),1),C:C,0))}
    {=INDEX(A:A,MATCH(LARGE(IF(B:B=52,C:C),2),C:C,0))}
    {=INDEX(A:A,MATCH(LARGE(IF(B:B=52,C:C),3),C:C,0))}

    ...etc all the way to 10.

    I now need to increase the conditions to also match value 1 in column D and 0 in column E (sample data attached). I would understand how to do this with a normal INDEX MATCH formula, but the LARGE function to get the highest sales has thrown me off. Does anyone know how to alter the formula to achieve this?

    Any help would be hugely appreciated! Thank you
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,278

    Re: Increase criteria for {=INDEX(A:A,MATCH(LARGE(IF(B:B=52,C:C),1),C:C,0))}

    See this version.
    Attached Files Attached Files
    Last edited by Czeslaw; 01-13-2016 at 04:47 PM.

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Increase criteria for {=INDEX(A:A,MATCH(LARGE(IF(B:B=52,C:C),1),C:C,0))}

    Another way. Array enter this in F2 fill down and across until you get blanks.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Dave

  4. #4
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,278

    Re: Increase criteria for {=INDEX(A:A,MATCH(LARGE(IF(B:B=52,C:C),1),C:C,0))}

    Fixed an error in the formula.
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Increase criteria for {=INDEX(A:A,MATCH(LARGE(IF(B:B=52,C:C),1),C:C,0))}

    Czeslaw Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Increase criteria for {=INDEX(A:A,MATCH(LARGE(IF(B:B=52,C:C),1),C:C,0))}

    albanhac, just a suggestion, but you should try and avoid using full-column ranges in an ARRAY formula, it will slow your file down. Rather, use just the range you need (or 2-3 times more if that will grow)

    The INDEX() part is outside of the ARRAY, so that part is OK

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Increase criteria for {=INDEX(A:A,MATCH(LARGE(IF(B:B=52,C:C),1),C:C,0))}

    FR & Czeslaw: neither of the answers provided are correct. They return the correct maximum value (£ 6,000), but return the first matching value in column A (49909, row 722) which has the configuration 52,0,0 and is incorrect given the criteria were 52,1,0. You need to add a further term inrto the MATCH to ensure that it returns the correct answer (54558, row 7975, configuration 52,1,0):

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    where the week number (52) is in I1.

    Albanhac, as Ford mentioned (and as I pointed out in your previous thread), you should NOT use whole column references in array formulae. It'll grind your PC to a halt if you have a "significant" number of rows. However in this case (unless you add in a correction term) you can't use a whole column reference for the iNDEX part as it returns a value 1 row above the correct one if you do!!

    the BEST option would be to use a non-volatile Named Range to adjust the column size automatically, using an INDEX formula. Do you know how to do that?
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  8. #8
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,278

    Re: Increase criteria for {=INDEX(A:A,MATCH(LARGE(IF(B:B=52,C:C),1),C:C,0))}

    Sorry, I did not realize the two conditions (value 1 in column D and 0 in column E).
    Attached Files Attached Files

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Increase criteria for {=INDEX(A:A,MATCH(LARGE(IF(B:B=52,C:C),1),C:C,0))}

    @ Glenn,

    Thank you for the heads-up. I forgot about the MATCH and duplicates.
    Worked for hours to find a correction without MATCH. All I found was more duplicates. LOL
    Looks like Czeslaw cracked it, though.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Increase criteria for {=INDEX(A:A,MATCH(LARGE(IF(B:B=52,C:C),1),C:C,0))}

    So did I (post 7), but Czeslaws might be a nicer way to do it...

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Increase criteria for {=INDEX(A:A,MATCH(LARGE(IF(B:B=52,C:C),1),C:C,0))}

    Quote Originally Posted by Glenn Kennedy View Post
    Czeslaws might be a nicer way to do it
    We can't tell.

    Whatever he did is "hidden" in a file.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Increase criteria for {=INDEX(A:A,MATCH(LARGE(IF(B:B=52,C:C),1),C:C,0))}

    Quote Originally Posted by Czeslaw View Post
    Sorry, I did not realize the two conditions (value 1 in column D and 0 in column E).
    Please read my post #5 regarding only posting a file

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] INDEX MATCH & LARGE Functions, Multiple Criteria
    By skyhawk3485 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 12-19-2018, 09:55 AM
  2. [SOLVED] Index Match with multiple criteria and selecting data from a large table
    By Aquarock in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-14-2014, 09:26 PM
  3. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  4. Excel 2007 : Index, Match, Large Formula: Multiple Criteria, Multiple Ranges
    By SimpleJack in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-19-2013, 08:54 AM
  5. Index / Match / Large based on a criteria.
    By SimpleJack in forum Excel General
    Replies: 7
    Last Post: 06-20-2012, 08:04 AM
  6. INDEX, MATCH and LARGE
    By ridebikes in forum Excel General
    Replies: 1
    Last Post: 10-24-2011, 04:03 PM
  7. Index, Match, and Large
    By edwardpestian in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-03-2006, 06:10 PM

Tags for this Thread

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