+ Reply to Thread
Results 1 to 2 of 2

Nested MIN(IF( statement within an INDEX MATCH

  1. #1
    Registered User
    Join Date
    01-28-2018
    Location
    Warwick, Rhode Island, USA
    MS-Off Ver
    2016
    Posts
    1

    Nested MIN(IF( statement within an INDEX MATCH

    Hi all,

    I was hoping someone out there could help me understand how exactly a piece of a relatively complex formula that someone a while back helped me on is working. Will do my best to explain.

    What the formula is doing successfully:
    Taking the Store Number, Item Number, Date, and Sales Amount listed in each record (4 different cells in the same row), and finding the closest Date to the one listed in said record that has a Price value for the same Store Number > Item Number combination on a separate table. It then takes the record's Sales Amount and Divides it by that found Price value to get a Sales Unit value.

    I've spot checked the results myself and can confirm the formula is working as it is designed to. However, I now have a need to understand how exactly it is doing that. There is a nested MIN(IF( statement within the formula that I can't quite wrap my head around as it seems to be going against my understanding of how these particular functions work.

    What the actual records look like (with formula values in last column):
    Record1.PNG

    What the table the formula is referencing looks like:
    Record2.PNG

    The formula within the first cell under "Total Inc Sales (Units)" column:
    {=opdata3[@[Total Inc Sales]]/INDEX('Item & Store Lookups'!$J$2:$J$109136,MATCH(MIN(IF((VALUE(opdata3[@[Store '#]])='Item & Store Lookups'!$H$2:$H$109136)*(IFERROR(VALUE(opdata3[@[ITEM CODE]]),opdata3[@[ITEM CODE]])='Item & Store Lookups'!$I$2:$I$109136),ABS(opdata3[@[Intervention Date]]-'Item & Store Lookups'!$G$2:$G$109136),9999)),IF((VALUE(opdata3[@[Store '#]])='Item & Store Lookups'!$H$2:$H$109136)*(IFERROR(VALUE(opdata3[@[ITEM CODE]]),opdata3[@[ITEM CODE]])='Item & Store Lookups'!$I$2:$I$109136),ABS(opdata3[@[Intervention Date]]-'Item & Store Lookups'!$G$2:$G$109136),9999),0))}

    The part I am having trouble understanding is immediately after the MATCH statement opens; specifically:
    MIN(IF((VALUE(opdata3[@[Store '#]])='Item & Store Lookups'!$H$2:$H$109136)*(IFERROR(VALUE(opdata3[@[ITEM CODE]]),opdata3[@[ITEM CODE]])='Item & Store Lookups'!$I$2:$I$109136),ABS(opdata3[@[Intervention Date]]-'Item & Store Lookups'!$G$2:$G$109136),9999))

    The IF statement doesn't make sense to me. The logical_test piece is taking the value of the Store Number "=Item & Store Lookups" then multiplying it by the Item Number "=Item & Store Lookups", but neither of those individual statements make sense to me as returning anything other than TRUE or FALSE, nor is it anything other than the multiplication of two numbers from what I can tell, which doesn't meet the criteria of a logical_test piece of an IF statement.

    Any help would be super appreciated. Let me know if I have not provided enough info.

  2. #2
    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: Nested MIN(IF( statement within an INDEX MATCH

    Hi Ceejus. Welcome to the forum.

    Those multiplied TRUEs/FALSEs apply a system of logic. For a breakdown of how that works:

    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    Those TRUE/FALSE returns 1/0 when math operations are performed on them. The net returns of each row of 1*1, 0*1, 1*0, 0*0 are themselves 1s and 0s. If returns TRUE for any non zero value and FALSE for 0s. Conditional upon each TRUE IF returns corresponding values in ABS(opdata3[@[Intervention Date]]-'Item & Store Lookups'!$G$2:$G$109136) Normally we see the FALSE returns of a formula like this to default to FALSE. MIN will ignore text. In this case the 9999 part appears from here to be an upper limit figure.



    MATCH matches the MIN it to that same IF (repeated) part. That returns the row number of the first match encountered.

    That in turn is passed to INDEX('Item & Store Lookups'!$J$2:$J$109136, so INDEX can return that row in 'Item & Store Lookups'!$J$2:$J$109136.

    I hope that helps.
    Dave

+ 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. Nested If Statement in INDEX MATCH
    By JammiBadger in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-10-2017, 12:41 PM
  2. VLOOKUP or INDEX-MATCH + INDIRECT with Nested IF/OR Statement
    By djmyers in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-11-2015, 10:40 PM
  3. [SOLVED] Nested IF Statement with INDEX and MATCH
    By utahguy9384 in forum Excel General
    Replies: 11
    Last Post: 05-23-2014, 03:41 PM
  4. [SOLVED] 3 Nested IF INDEX MATCH
    By JonesZoid in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-09-2014, 10:56 AM
  5. [SOLVED] Combining Nested IF statement with Index/Match??
    By consulttk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-21-2012, 09:48 AM
  6. nested if statement with index and match within
    By darkowen in forum Excel General
    Replies: 3
    Last Post: 02-01-2012, 02:02 AM
  7. Nested Match(index()) vs. Match() array formula.
    By GeneralDisarray in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2011, 07:57 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