+ Reply to Thread
Results 1 to 7 of 7

INDEX MATCH LARGE with Multiple criteria

  1. #1
    Registered User
    Join Date
    08-22-2013
    Location
    Milan
    MS-Off Ver
    Excel 2010
    Posts
    14

    INDEX MATCH LARGE with Multiple criteria

    I am trying to get the largest value in a column that is also higher than a value available in another column and lower than a value available in another column.
    The problem is that I don't know which are the values of reference in the other columns
    For example, if you look at the attachment, the result I need to get is the value in column A with highest qty in column B but whose price be higher than corresponding value in column C and lower than corresponding value in column D
    In the example, the result will be 140, because it is true that highest qty in column B is 70, but the corresponding Act Price (890) is higher than the corresponding Max Price in column D (220)
    If then I would change for example the qty in cell B3 to 70, the result of the formula would be 130.
    I tried with the following formula but I don't how to tell to excel to find the correct row in the range (if I change the qty to 70 in cell B3 the result is still 140)

    =INDEX($A$2:$A$7; LARGE(IF(($A$2:$A$7>$C$3:$C$8)*($A$2:$A$7<$D$2:$D$7); ROW($B$2:$B$7)-2; ""); 1))
    how can I fix it?
    Thank you
    Attached Files Attached Files

  2. #2
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: INDEX MATCH LARGE with Multiple criteria

    If you just make the formula an array-formula the result will be 890.
    Close the formula with [Ctrl]+[Shift]+[Enter]
    That's necessary because the formula uses the IF-function which is not array capable in itself.
    Also change $C$3:$C$8 to $C$2:$C$7
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  3. #3
    Registered User
    Join Date
    08-22-2013
    Location
    Milan
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: INDEX MATCH LARGE with Multiple criteria

    Thanks Tsjallie, you're right there is a typing mistake, but the result I want to achieve is not 890.
    890 is the highest qty in column B but 890 is higher than the corresponding max price (220).
    Probably the formula is totally wrong.
    Do you know how I can calculate the correct value?

  4. #4
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: INDEX MATCH LARGE with Multiple criteria

    I think this is the formula you need (close with [ctrl]+[shift]+[enter]):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This formula is composed of 4 steps:

    1. Find the row satifiying both conditions
    Result of this part is an index with the indexes of the rows satisfying the conditions: {0;2;3;0;0;6}
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    2. Find the largest Qty of the rows satifying both conditions
    Result is the largest Qty of the rows satisfying the conditions: 60
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    3. Find the row(s) having the largest Qty and satifying both conditions
    With your data that results in: 2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    4. Lookup the Act Price with the index found in step 3
    This will return 130
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: INDEX MATCH LARGE with Multiple criteria

    And here's the workbook.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-22-2013
    Location
    Milan
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: INDEX MATCH LARGE with Multiple criteria

    Amazing! This perfectly works!
    Thank you!

  7. #7
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: INDEX MATCH LARGE with Multiple criteria

    Glad I could help. Did make me sweat for a moment
    If everything is Ok pls mark this thread SOLVED (see thread tools in the menu above).

+ 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] Using LARGE or MAX inside INDEX-MATCH formula with multiple criteria
    By Big.Moe in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-28-2017, 02:40 AM
  3. Index Match Large formula with multiple criteria
    By Dylan Cooper in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-21-2016, 06:00 PM
  4. Increase criteria for {=INDEX(A:A,MATCH(LARGE(IF(B:B=52,C:C),1),C:C,0))}
    By albanhac in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-14-2016, 07:31 PM
  5. [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
  6. 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
  7. Index / Match / Large based on a criteria.
    By SimpleJack in forum Excel General
    Replies: 7
    Last Post: 06-20-2012, 08:04 AM

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