Find one possible match in a matrix (3 criteria, array formula)

[TimvMechelen]

Hi All,

The situation that I have is the following:

Sheet1: (start and end times of orders)

Machine (CELL B1)	OrderNumber (CELL E1)	StartTime (CELL P1)	EndTime (CELL Q1)
Machine1	00001
Machine2	00002
Machine3	00003
Machine4	00004
Machine1	00005
Machine2	00006
Machine3	00007
Machine1	00008
Machine4	00009

Sheet2: (given downtimes)

Machine (Cell A1)	StartTime (Cell B1)	EndTime (Cell C1)	OrderNumber (Cell D1)
Machine1			(Here I need the formula to look up the OrderNumber)
Machine2
Machine3
Machine4

The array formula I have right now is the following:
{=INDEX(SHEET1!$E$2:$E$16921,SMALL(IF((SHEET1!$P$2:$P$16921<=$B2)*(SHEET1!$Q$2:$Q$16921>=$C2),ROW(SHEET1!$P$2:$P$16921)-ROW(SHEET1!$P$2)+1,"");1))}
( DUTCH formula: {=INDEX(Blad1!$E$2:$E$16921;KLEINSTE(ALS((Blad1!$P$2:$P$16921<=$B2)*(Blad1!$Q$2:$Q$16921>=$C2);RIJ(Blad1!$P$2:$P$16921)-RIJ(Blad1!$P$2)+1;"");1))} )

So what I try to do here is to find the matching ordernumber which meets the following requirements:

• The starttime in sheet1 should be smaller or equal to the starttime in sheet2 (in the same row as the formula);
• The endtime in sheet1 should be bigger or equal to the endtime in sheet2 (in the same row as the formula);
• The machine in sheet1 should be equal to the machine in sheet2 (the machine of the row of the formula) (this is not part of the formula right now)

I can search on the first two criteria, but then I'll get multiple results. I want only one result which matches the machine from the row in sheet2.

Can somebody help me with my question? Would appreciate it a lot (and to learn more about array formulas)

If something is not clear, please let me know.

Thank you!

 

[FatBoyClam]

You're on the right lines.

(SHEET1!$P$2:$P$16921<=$B2) effectively creates an array of true/false values for each row referenced in Sheet1 column P being less than the value in Sheet2
(SHEET1!$Q$2:$Q$16921>=$C2) does the same for the rows in Sheet1 column Q being greater than the value in Sheet2
multiplying them together creates an array the same size. Where both the above arrays return true, this third array will be true, otherwise it will be false.

If you take that to the logical next step...
(SHEET1!$B$2:$B$16921=$A2) is an array where the rows from sheet1 match the machine from Sheet2

So, putting all those together:

(SHEET1!$P$2:$P$16921<=$B2)*(SHEET1!$Q$2:$Q$16921>=$C2)*(SHEET1!$B$2:$B$16921=$A2) is an array where the Machine matches from Sheet2, the StartTime is less than that on Sheet2 and the EndTime is greater than that on Sheet2.

You can now use that array in a Match formula;

MATCH(1, (SHEET1!$P$2:$P$16921<=$B2)*(SHEET1!$Q$2:$Q$16921>=$C2)*(SHEET1!$B$2:$B$16921=$A2) ,0)

This is looking to return the ordinal position of 1 (or TRUE) in your calculated array.

Use that as in an INDEX function to get the value from a range of matching size (in this case, the corresponding rows in Column E from sheet1);

=INDEX(SHEET1!$E$2:$E$16921, MATCH(1, (SHEET1!$P$2:$P$16921<=$B2)*(SHEET1!$Q$2:$Q$16921>=$C2)*(SHEET1!$B$2:$B$16921=$A2) ,0))

Enter that as an array formula, and you're away.

Note: if there is no Order that meets your criteria the MATCH function will return an error, and your INDEX function will return an error, so be prepared to handle that.

 

[TimvMechelen]

Thank you! It is working!