+ Reply to Thread
Results 1 to 9 of 9

Using LARGE or MAX inside INDEX-MATCH formula with multiple criteria

  1. 02-23-2017, 08:29 PM #1
    Big.Moe
    Big.Moe is offline
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Using LARGE or MAX inside INDEX-MATCH formula with multiple criteria

    I've got an INDEX-MATCH that is working just great, but then I realized that I will most likely have the condition where I have more than one result that is under 100 (see below). So, I'm trying (without success) to incorporate LARGE into the formula in order to get the largest number below 100.

    EDIT: At this point, I've reached the end of my Excel rope and completely flabbergasted.
    Any help, suggestions, or nudges no matter how small would be greatly appreciated.

    Here is the formula that worked great without LARGE:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    This was my last unsuccessful attempt at incorporating LARGE into the formula:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    EDIT: Here is the formula suggested to me using MAX instead of LARGE that worked great in F9, but returns #N/A errors in subsequent cells:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    EDIT: I've attached a reduced version of the file below.

    Here's a quick screen grab:
    Formula.jpg
    Attached Files Attached Files
    Last edited by Big.Moe; 02-28-2017 at 12:06 AM. Reason: Update Info
    Reply With QuoteRegister To Reply
  2. 02-23-2017, 08:55 PM #2
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Using LARGE inside INDEX-MATCH formula with multiple criteria

    Try array entering this formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Dave
    Reply With QuoteRegister To Reply
  3. 02-23-2017, 09:06 PM #3
    Big.Moe
    Big.Moe is offline
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Re: Using LARGE inside INDEX-MATCH formula with multiple criteria

    You sir are a bonafide Excel savior! Works beautifully!
    Reply With QuoteRegister To Reply
  4. 02-23-2017, 10:30 PM #4
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Using LARGE inside INDEX-MATCH formula with multiple criteria

    Good to hear! Thank you for the feedback and the rep.
    Reply With QuoteRegister To Reply
  5. 02-24-2017, 03:30 AM #5
    Big.Moe
    Big.Moe is offline
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Re: Using LARGE inside INDEX-MATCH formula with multiple criteria

    Unfortunately, I spoke a little too soon. While playing around with numbers and different scenarios, I came across a really weird error. I've studied and worked at this for a couple hours now and I can't make any sense of it, so I'm coming back to the forum with my tail between the legs begging for help once again.

    I don't think it's a problem with the formula, but I've gone over and over my spreadsheet formatting, deleted values and formulas and re-entered, etc. and still no fixes. Hell, I would do some sort of Excel dance around my chair if it would help. No matter what I do I get strange "Value Not Available" errors, but I can't even find a logical pattern to the errors.

    HEEEEEELP!!

    I've attached a shortened version of my file for anyone to look at.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 02-24-2017, 05:35 PM #6
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Using LARGE inside INDEX-MATCH formula with multiple criteria

    Hmm.

    Going to need more details than that.
    1. In what sheet are the errors you refer to?
    2. What cell address(es)?
    3. Now that I have context I also think I see a problem with the formula.
    4. Besides the #REF! errors (a year in the DATE formula I2 fixes that) all I see are #N/A errors. Those are the formula.
    5. What else?
    Reply With QuoteRegister To Reply
  7. 02-26-2017, 02:32 AM #7
    Big.Moe
    Big.Moe is offline
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Re: Using LARGE inside INDEX-MATCH formula with multiple criteria

    FlameRetired, I'm not ignoring your questions and I haven't forgotten about you.

    I've been thrown into the Excel deep end by my boss and I'm struggling to keep up. I'm in no way an Excel pro, but I'm the best in the office, so I'm it by default.

    So, they gave me yet another Excel project that seems to be a hotter deal than the current one I was working on. As soon as I finish this other thing, I'll get back to you about this one. lol
    Reply With QuoteRegister To Reply
  8. 02-27-2017, 04:39 PM #8
    Big.Moe
    Big.Moe is offline
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Question Re: Using LARGE inside INDEX-MATCH formula with multiple criteria

    Okay, I'm back onto this project....sorry about the delay.

    For some reason, the formula FlameRetired furnished works fabulously in cell F9 on sheet "Daily - Jan", but when that same formula is entered in F10 through F13, it no longer works (#N/A errors). I've looked over the cells on sheet "Inventory" that the formula references, but cannot find anything that would cause the difference in outcome from cell to cell.

    If FlameRetired or anyone else would you have the time to take a quick look I would be much appreciative.
    I'm positive it has nothing to do with the formula itself, but at this point, I'm completely flabbergasted.

    Formula.jpg
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 02-28-2017, 02:40 AM #9
    Big.Moe
    Big.Moe is offline
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Re: Using LARGE or MAX inside INDEX-MATCH formula with multiple criteria

    This was eventually solved in a new post here:
    http://www.excelforum.com/excel-form...-embedded.html

    FlameRetired was da Man & figured it out once again.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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. 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
  3. 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
  4. [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
  5. [SOLVED] Putting OR inside a INDEX SMALL formula for multiple criteria, can it be done?
    By whetu in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-22-2014, 06:56 AM
  6. Formula needed for partial string match as part of index match with multiple criteria
    By Marvelous in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  7. 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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1