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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,415
Messages
6,130,465
Members
449,583
Latest member
CatMama9

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
Back
Top