# Nested MIN(IF( statement within an INDEX MATCH

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.  Register To Reply

2. ## 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.  Register To Reply