Min If Array Help

Kerrymr

New Member
Joined
Sep 5, 2014
Messages
4
Hi All,

I am working with an excel array to determine the minimum ship date from a series of orders and forecast. There are two tables, one represents orders the other forecast. Both tables have the same headers, Customer, material, and Ship Date. I am trying to use an array formula to return the ship date for each customer material combination. If there is no active order, then the result should search the forecast table for an active forecast.

The data looks something like this....
(reference cell A2 is the word customer in the order table and cell E3 is the word customer in the forecast table.)

Orders Table
Customer Material Ship Date
A 3 9/5/2014
A 3 11/25/2014
A 3 12/29/2014
A 1 9/8/2014
A 1 9/26/2014
A 1 10/7/2014

Forecast Table
Customer Material Ship Date
A 1 9/1/2014
A 1 10/1/2014
A 1 12/1/2014
A 2 10/1/2014
A 2 11/1/2014
A 2 12/1/2014

Results (Array formula below)
A 1 9/1/2014
{=MIN(IF($A12&$B12=$A$3:$A$8&$B$3:$B$8,$C$3:$C$8,IF($A12&$B12=$E$3:$E$8&$F$3:$F$8,$G$3:$G$8,"")))}
A 2 10/1/2014
{=MIN(IF($A13&$B13=$A$3:$A$8&$B$3:$B$8,$C$3:$C$8,IF($A13&$B13=$E$3:$E$8&$F$3:$F$8,$G$3:$G$8,"")))}
A 3 9/5/2014
{=MIN(IF($A14&$B14=$A$3:$A$8&$B$3:$B$8,$C$3:$C$8,IF($A14&$B14=$E$3:$E$8&$F$3:$F$8,$G$3:$G$8,"")))}


When I use this formula, it returns the result of the first result found.

For example, customer A, material 1 should return 9/5/2014, however it appears that since A1 is in row 6 in the orders table and A1 is in row 1 in the forecast table, it is returning the forecast value.

what is the correct way to write this array?
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to MrExcel.

Formula in C12 copied down:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Orders Table</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Forecast Table</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Customer</td><td style=";">Material</td><td style=";">Ship Date</td><td style="text-align: right;;"></td><td style=";">Customer</td><td style=";">Material</td><td style=";">Ship Date</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">A</td><td style="text-align: right;;">3</td><td style="text-align: right;;">05/09/2014</td><td style="text-align: right;;"></td><td style=";">A</td><td style="text-align: right;;">1</td><td style="text-align: right;;">01/09/2014</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">A</td><td style="text-align: right;;">3</td><td style="text-align: right;;">25/11/2014</td><td style="text-align: right;;"></td><td style=";">A</td><td style="text-align: right;;">1</td><td style="text-align: right;;">01/10/2014</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">A</td><td style="text-align: right;;">3</td><td style="text-align: right;;">29/12/2014</td><td style="text-align: right;;"></td><td style=";">A</td><td style="text-align: right;;">1</td><td style="text-align: right;;">01/12/2014</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">A</td><td style="text-align: right;;">1</td><td style="text-align: right;;">08/09/2014</td><td style="text-align: right;;"></td><td style=";">A</td><td style="text-align: right;;">2</td><td style="text-align: right;;">01/10/2014</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">A</td><td style="text-align: right;;">1</td><td style="text-align: right;;">26/09/2014</td><td style="text-align: right;;"></td><td style=";">A</td><td style="text-align: right;;">2</td><td style="text-align: right;;">01/11/2014</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">A</td><td style="text-align: right;;">1</td><td style="text-align: right;;">07/10/2014</td><td style="text-align: right;;"></td><td style=";">A</td><td style="text-align: right;;">2</td><td style="text-align: right;;">01/12/2014</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">A</td><td style="text-align: right;;">1</td><td style="text-align: right;;">01/09/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">A</td><td style="text-align: right;;">2</td><td style="text-align: right;;">01/10/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">A</td><td style="text-align: right;;">3</td><td style="text-align: right;;">05/09/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C12</th><td style="text-align:left">{=MIN(<font color="Blue">IF(<font color="Red">A$3:A$8=A12,IF(<font color="Green">B$3:B$8=B12,C$3:C$8</font>)</font>),IF(<font color="Red">E$3:E$8=A12,IF(<font color="Green">F$3:F$8=B12,G$3:G$8</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Kerrymr

New Member
Joined
Sep 5, 2014
Messages
4
Thanks for the quick response!
I note however, that the result of your array returns value 1/9/2014 for customer A, material 1.
I am trying to have the array return the result from the orders table when there is an order.
In the example you provided it returns the result from the forecast table when an order exists.

Any thoughts?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Does this work for you?

=MIN(IF(COUNTIFS(A$3:A$8,A12,B$3:B$8,B12),IF(A$3:A$8=A12,IF(B$3:B$8=B12,C$3:C$8)),IF(E$3:E$8=A12,IF(F$3:F$8=B12,G$3:G$8))))

Don't forget Ctrl+Shift+Enter.
 

Peter Thompson

Active Member
Joined
Dec 15, 2008
Messages
262
I would use a helper column in column A and then break the element of the array into separate cells

Helper Column (inn a3 down to the calculation rows)
=D3&C3

E12 {=IF(COUNTIF(A$3:A$8,A12)>0,MIN(IF(A$3:A$8=A12,E$3:E$8,999999999)),999999999) }

F12 {=MIN(IF(F$3:F$8=A12,I$3:I$8,999999999))}

G12 =IF(AND(E13>0,E13<>999999999),E13,F13)

Note the 999999999 is just an arbitary large number to ensure that it doesn't get picked.
 

Kerrymr

New Member
Joined
Sep 5, 2014
Messages
4
This solution worked.
=MIN(IF(COUNTIFS(A$3:A$8,A12,B$3:B$8,B12),IF(A$3:A$8=A12,IF(B$3:B$8=B12,C$3:C$8)),IF(E$3:E$8=A12,IF(F$3:F$8=B12,G$3:G$8) )))

Why did the first one fail? Like mine, I would have expected it to work.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,820
Messages
5,598,301
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top