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?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Andrew Poulsom

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

Formula in C12 copied down:


Excel 2010
ABCDEFG
1Orders TableForecast Table
2CustomerMaterialShip DateCustomerMaterialShip Date
3A305/09/2014A101/09/2014
4A325/11/2014A101/10/2014
5A329/12/2014A101/12/2014
6A108/09/2014A201/10/2014
7A126/09/2014A201/11/2014
8A107/10/2014A201/12/2014
9
10
11
12A101/09/2014
13A201/10/2014
14A305/09/2014
Sheet1
Cell Formulas
RangeFormula
C12{=MIN(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)))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,026
Messages
5,835,024
Members
430,332
Latest member
Charly_Moon

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