Index Match

[L4z3YB0n3z]

I am trying to look up in the row when the number becomes negative and return the value in one column and the date in another column.
For example: Fire Hose Goes Negative on November 10, -45.
Is it possible to do this for hundreds of parts?

RowLabels

Factory OH

1-Jan

4-Jan

5-Jan

6-Jan

7-Jan

8-Jan

11-Jan

12-Jan

13-Jan

14-Jan

15-Jan

19-Jan

20-Jan

21-Jan

22-Jan

23-Jan

25-Jan

26-Jan

27-Jan

28-Jan

29-Jan

1-Feb

2-Feb

3-Feb

4-Feb

5-Feb

6-Feb

8-Feb

9-Feb

10-Feb

11-Feb

12-Feb

15-Feb

16-Feb

17-Feb

18-Feb

19-Feb

22-Feb

23-Feb

24-Feb

25-Feb

26-Feb

27-Feb

1-Mar

2-Mar

3-Mar

4-Mar

5-Mar

6-Mar

8-Mar

9-Mar

10-Mar

11-Mar

12-Mar

15-Mar

16-Mar

17-Mar

18-Mar

19-Mar

22-Mar

23-Mar

24-Mar

25-Mar

26-Mar

29-Mar

30-Mar

31-Mar

1-Apr

4-Apr

5-Apr

6-Apr

7-Apr

8-Apr

9-Apr

10-Apr

11-Apr

12-Apr

13-Apr

14-Apr

15-Apr

16-Apr

18-Apr

19-Apr

20-Apr

21-Apr

22-Apr

23-Apr

25-Apr

26-Apr

27-Apr

28-Apr

29-Apr

30-Apr

2-May

3-May

4-May

5-May

6-May

7-May

9-May

10-May

11-May

12-May

13-May

14-May

16-May

17-May

18-May

19-May

20-May

21-May

23-May

24-May

25-May

26-May

27-May

28-May

31-May

1-Jun

2-Jun

3-Jun

4-Jun

6-Jun

7-Jun

8-Jun

9-Jun

10-Jun

11-Jun

13-Jun

14-Jun

15-Jun

16-Jun

17-Jun

18-Jun

20-Jun

21-Jun

22-Jun

23-Jun

24-Jun

25-Jun

27-Jun

28-Jun

29-Jun

30-Jun

1-Jul

2-Jul

5-Jul

6-Jul

7-Jul

8-Jul

9-Jul

11-Jul

12-Jul

13-Jul

14-Jul

15-Jul

16-Jul

18-Jul

19-Jul

20-Jul

21-Jul

22-Jul

23-Jul

25-Jul

26-Jul

27-Jul

28-Jul

29-Jul

30-Jul

1-Aug

2-Aug

3-Aug

4-Aug

5-Aug

6-Aug

8-Aug

9-Aug

10-Aug

11-Aug

12-Aug

13-Aug

15-Aug

16-Aug

17-Aug

18-Aug

19-Aug

20-Aug

22-Aug

23-Aug

24-Aug

25-Aug

26-Aug

27-Aug

29-Aug

30-Aug

31-Aug

1-Sep

2-Sep

3-Sep

6-Sep

7-Sep

8-Sep

9-Sep

10-Sep

12-Sep

13-Sep

14-Sep

15-Sep

16-Sep

17-Sep

20-Sep

21-Sep

22-Sep

23-Sep

24-Sep

27-Sep

28-Sep

29-Sep

30-Sep

1-Oct

4-Oct

5-Oct

6-Oct

7-Oct

8-Oct

11-Oct

12-Oct

13-Oct

14-Oct

15-Oct

18-Oct

19-Oct

20-Oct

21-Oct

22-Oct

25-Oct

26-Oct

27-Oct

28-Oct

29-Oct

1-Nov

2-Nov

3-Nov

4-Nov

5-Nov

8-Nov

9-Nov

10-Nov

11-Nov

12-Nov

15-Nov

16-Nov

17-Nov

18-Nov

19-Nov

21-Nov

22-Nov

23-Nov

24-Nov

28-Nov

29-Nov

30-Nov

Fire Hose

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

47​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

46​

42​

42​

42​

42​

42​

-45​

-48​

-48​

-58​

-60​

-60​

-60​

-60​

-60​

-72​

-87​

-87​

-87​

-89​

-92​

Helmet

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

32​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

30​

Coate

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

14​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

12​

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ	FA	FB	FC	FD	FE	FF	FG	FH	FI	FJ	FK	FL	FM	FN	FO	FP	FQ	FR	FS	FT	FU	FV	FW	FX	FY	FZ	GA	GB	GC	GD	GE	GF	GG	GH	GI	GJ	GK	GL	GM	GN	GO	GP	GQ	GR	GS	GT	GU	GV	GW	GX	GY	GZ	HA	HB	HC	HD	HE	HF	HG	HH	HI	HJ	HK	HL	HM	HN	HO	HP	HQ	HR	HS	HT	HU	HV	HW	HX	HY	HZ	IA	IB	IC	ID	IE	IF	IG	IH	II	IJ	IK	IL	IM	IN	IO	IP	IQ	IR	IS	IT	IU	IV	IW	IX	IY	IZ	JA	JB	JC	JD	JE	JF	JG
1	RowLabels	Factory OH	01-Jan	04-Jan	05-Jan	06-Jan	07-Jan	08-Jan	11-Jan	12-Jan	13-Jan	14-Jan	15-Jan	19-Jan	20-Jan	21-Jan	22-Jan	23-Jan	25-Jan	26-Jan	27-Jan	28-Jan	29-Jan	01-Feb	02-Feb	03-Feb	04-Feb	05-Feb	06-Feb	08-Feb	09-Feb	10-Feb	11-Feb	12-Feb	15-Feb	16-Feb	17-Feb	18-Feb	19-Feb	22-Feb	23-Feb	24-Feb	25-Feb	26-Feb	27-Feb	01-Mar	02-Mar	03-Mar	04-Mar	05-Mar	06-Mar	08-Mar	09-Mar	10-Mar	11-Mar	12-Mar	15-Mar	16-Mar	17-Mar	18-Mar	19-Mar	22-Mar	23-Mar	24-Mar	25-Mar	26-Mar	29-Mar	30-Mar	31-Mar	01-Apr	04-Apr	05-Apr	06-Apr	07-Apr	08-Apr	09-Apr	10-Apr	11-Apr	12-Apr	13-Apr	14-Apr	15-Apr	16-Apr	18-Apr	19-Apr	20-Apr	21-Apr	22-Apr	23-Apr	25-Apr	26-Apr	27-Apr	28-Apr	29-Apr	30-Apr	02-May	03-May	04-May	05-May	06-May	07-May	09-May	10-May	11-May	12-May	13-May	14-May	16-May	17-May	18-May	19-May	20-May	21-May	23-May	24-May	25-May	26-May	27-May	28-May	31-May	01-Jun	02-Jun	03-Jun	04-Jun	06-Jun	07-Jun	08-Jun	09-Jun	10-Jun	11-Jun	13-Jun	14-Jun	15-Jun	16-Jun	17-Jun	18-Jun	20-Jun	21-Jun	22-Jun	23-Jun	24-Jun	25-Jun	27-Jun	28-Jun	29-Jun	30-Jun	01-Jul	02-Jul	05-Jul	06-Jul	07-Jul	08-Jul	09-Jul	11-Jul	12-Jul	13-Jul	14-Jul	15-Jul	16-Jul	18-Jul	19-Jul	20-Jul	21-Jul	22-Jul	23-Jul	25-Jul	26-Jul	27-Jul	28-Jul	29-Jul	30-Jul	01-Aug	02-Aug	03-Aug	04-Aug	05-Aug	06-Aug	08-Aug	09-Aug	10-Aug	11-Aug	12-Aug	13-Aug	15-Aug	16-Aug	17-Aug	18-Aug	19-Aug	20-Aug	22-Aug	23-Aug	24-Aug	25-Aug	26-Aug	27-Aug	29-Aug	30-Aug	31-Aug	01-Sep	02-Sep	03-Sep	06-Sep	07-Sep	08-Sep	09-Sep	10-Sep	12-Sep	13-Sep	14-Sep	15-Sep	16-Sep	17-Sep	20-Sep	21-Sep	22-Sep	23-Sep	24-Sep	27-Sep	28-Sep	29-Sep	30-Sep	01-Oct	04-Oct	05-Oct	06-Oct	07-Oct	08-Oct	11-Oct	12-Oct	13-Oct	14-Oct	15-Oct	18-Oct	19-Oct	20-Oct	21-Oct	22-Oct	25-Oct	26-Oct	27-Oct	28-Oct	29-Oct	01-Nov	02-Nov	03-Nov	04-Nov	05-Nov	08-Nov	09-Nov	10-Nov	11-Nov	12-Nov	15-Nov	16-Nov	17-Nov	18-Nov	19-Nov	21-Nov	22-Nov	23-Nov	24-Nov	28-Nov	29-Nov	30-Nov
2	Fire Hose	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	42	42	42	42	42	-45	-48	-48	-58	-60	-60	-60	-60	-60	-72	-87	-87	-87	-89	-92		10-Nov	-45
3	Helmet	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30		no -ve	no -ve
4	Coate	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12		no -ve	no -ve
Main

Cell Formulas
Range	Formula
JF2:JF4	JF2	=INDEX(FILTER($B$1:$JD$1,B2:JD2<0,"no -ve"),1)
JG2:JG4	JG2	=INDEX(FILTER(B2:JD2,B2:JD2<0,"no -ve"),1)

 

[L4z3YB0n3z]

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[L4z3YB0n3z]

I have another question. Is it possible to return the Next Date and quantity following the day it does negative? IN the example above it goes Negative 11/10 for 45 pcs but I want the 11/11 date for 48 pcs

 

[Fluff]

Just change the 1 at the end of the formula to 2

 

[L4z3YB0n3z]

where is the face palm emoji? Thanks

 

[Fluff]

You're welcome & thanks for the feedback.

where is the face palm emoji?

Just here ?‍