Column/ Array comparison for first instance where column A less than column B

[keatsc]

Date		Committed		OnHand
28/11/2018	1			11
29/11/2018	3			10
30/11/2018	3			9
01/12/2018	4			8
02/12/2018	2			7
03/12/2018	5			8
04/12/2018	6			10
05/12/2018	8			6
06/12/2018	4			8
07/12/2018	5			5
08/12/2018	6			3
09/12/2018	7			8

Hi there,

I'm trying to compare a list of values in the Committed column against a list of values in the OnHand Column to get a single corresponding date value from the first related row.

I want to return the date value on the row which has the first instance of the value in the Committed Column being higher than the value in the OnHand Column. So the correct answer would be 05/12/2018 but not 08/12/2018 in the example above.

My Index Match formula looks for a set value but I don't know what the value in the column is or would be so that's no use to me. I tried putting in another index match in place of the fixed value but it errored. I tried matching against True but that complained as well.

Any suggestions appreciated. Not sure if I'm even in the right ballpark with my formula attempts. Let me know if more info is required.

Thanks for looking.

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D
16	Date	Committed	OnHand
17	28/11/2018	1	11	05/12/2018
18	29/11/2018	3	10
19	30/11/2018	3	9
20	01/12/2018	4	8
21	02/12/2018	2	7
22	03/12/2018	5	8
23	04/12/2018	6	10
24	05/12/2018	8	6
25	06/12/2018	4	8
26	07/12/2018	5	5
27	08/12/2018	6	3
28	09/12/2018	7	8
Archive

Cell Formulas
Range	Formula
D17	{=INDEX(A17:A28,MATCH(1,--(B17:B28>C17:C28),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[keatsc]

That works perfectly thank you. Can you check my understanding below is correct?

{=INDEX(A17:A28,MATCH(1,--(B17:B28>C17:C28),0))}

After the Match, "1" is the value matched against so is that related to an array of something like 0,0,1,0 for column B greater than column C and the "0" at the end is the first instance of 1 in that array?

What are the "--" for?

Thanks for your help.

 

[Fluff]

This part
(B17:B28>C17:C28)
will return
FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE
the -- then converts that to
0;0;0;0;0;0;0;1;0;0;1;0
Then the
MATCH(1,
is specifying that we want to find 1 in the above array
and the 0 at the end is saying it needs to be an exact match.

HTH

 

[keatsc]

Ah I see. Thank you very much for your time and the explanation.

 

[Fluff]

Glad to help & thanks for the feedback