Index Match Negative Number and Pull Date

[mediumraresteak]

Hi Everyone,

I can't figure this out and I wonder if IndexMatch is the wrong formula. I'm trying to find two results: 1) Pull the first negative for specific SKU and 2) Pull the corresponding date.

Results should be as below:

SKU	Date	Shortage
Hat	8/1/21	-20
Bike	-	-
Shoe	-	-

Data Dump:

Date	SKU	Balance
7/16/21	Hat	5
7/20/21	Bike	10
7/30/21	Shoe	15
8/1/21	Hat	-20

 

[mediumraresteak]

The formula below pulls the first negative number and gives me the corresponding date, but doesn't look for the specific sku. Same issue with finding the first negative line as it doesn't look based on the SKU.
=INDEX(A:A,MATCH(TRUE,INDEX(C:C<0,0,1),0))

 

[maabadi]

Please Update your Account Details and save it to we know what version of Excel & OS you used & then we can help better based on.
Try these formulas with CTRL+SHIFT+ENTER

Book1
	A	B	C	D	E	F	G	H
1	Date	SKU	Balance
2	7/16/2021	Hat	5		Hat	8/1/2021	-20
3	7/20/2021	Bike	10		Bike	8/9/2021	-8
4	7/30/2021	Shoe	15		Shoe	8/5/2021	-3
5	8/1/2021	Hat	-20
6	8/3/2021	Bike	8
7	8/5/2021	Shoe	-3
8	8/7/2021	Hat	-12
9	8/9/2021	Bike	-8
10	8/11/2021	Shoe	6
11	8/13/2021	Hat	4
12	8/15/2021	Bike	12
13	8/17/2021	Shoe	-10
14
Sheet2

Cell Formulas
Range	Formula
F2:F4	F2	=INDEX($A$2:$C$13,MATCH(1,($B$2:$B$13=E2)*($C$2:$C$13<0),0),1)
G2:G4	G2	=INDEX($A$2:$C$13,MATCH(1,($B$2:$B$13=$E2)*($C$2:$C$13<0),0),3)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Radoslaw Poprawski]

1) Pull the first negative for specific SKU

I would ask:
1st negative from the top?
or maybe do mean minimum negative value?
or first negative from the end?

ur data sample is very small and thus those questions.

 

[mediumraresteak]

This was it! Thank you! My index was incorrect and set to only first column on my earlier tests.

 

[mediumraresteak]

Please Update your Account Details and save it to we know what version of Excel & OS you used & then we can help better based on.
Try these formulas with CTRL+SHIFT+ENTER

Book1
	A	B	C	D	E	F	G	H
1	Date	SKU	Balance
2	7/16/2021	Hat	5		Hat	8/1/2021	-20
3	7/20/2021	Bike	10		Bike	8/9/2021	-8
4	7/30/2021	Shoe	15		Shoe	8/5/2021	-3
5	8/1/2021	Hat	-20
6	8/3/2021	Bike	8
7	8/5/2021	Shoe	-3
8	8/7/2021	Hat	-12
9	8/9/2021	Bike	-8
10	8/11/2021	Shoe	6
11	8/13/2021	Hat	4
12	8/15/2021	Bike	12
13	8/17/2021	Shoe	-10
14
Sheet2

Cell Formulas
Range	Formula
F2:F4	F2	=INDEX($A$2:$C$13,MATCH(1,($B$2:$B$13=E2)*($C$2:$C$13<0),0),1)
G2:G4	G2	=INDEX($A$2:$C$13,MATCH(1,($B$2:$B$13=$E2)*($C$2:$C$13<0),0),3)
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you!

 

[maabadi]

You're Welcome & thanks for Feedback.