excel formula to find first cell with value in row

[NORRILLOUS]

I have been looking through the forums but haven't found what I'm looking for exactly.

S​	T​	U​	V​	W​	X​
Ship Date	Ship Date	Ship Date	Ship Date	Ship Date	Ship Date
3/29/2021​	3/30/2021​	3/31/2021​	4/1/2021​	4/2/2021​	4/5/2021​
-	960,000	-	240,000	-	-

Above I am showing what my spread sheet looks like, I am trying to look at the bottom row and find the first number >0 and return the ship date above it. So for the example above it would find the number "960,000" and return "3/30/21"

I have figured out how to find the number but I don't know how to change the return value. (Code Below)

=IF(R3=0,0,INDEX(S3:X3,MATCH(TRUE,S3:X3>0,0)))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[NORRILLOUS]

Depending on your version of excel, see if either of these work for you

=MINIFS(S2:X2,S3:X3,">0")

=AGGREGATE(15,6,S2:X2/SIGN(S3:X3),1)

Acutally the

=MINIFS(S2:X2,S3:X3,">0")

option works flawlessly, how do I get around the "-" issue though?

 

[kweaver]

Are you typing in the dash or using custom formatting for a 0 to show as a -? The latter should enable this to work.

 

[jasonb75]

It should still work either way, the *IFS functions ignore text when the criteria is numeric so it should only be picking up +ve numeric values, nothing else.

That said, the numbers are left aligned, indicating that they could actually be text strings instead of valid numbers.

Try this one, possibly needs to be array confirmed with Ctrl Shift Enter if not using office 365 with dynamic arrays.

=AGGREGATE(15,6,S2:X2/SIGN(S3:X3+0),1)

 

[Snakehips]

Book1
	P	Q	R	S	T	U	V	W
1				Ship Date	Ship Date	Ship Date	Ship Date	Ship Date
2				3/29/2021	3/30/2021	3/31/2021	4/1/2021	4/2/2021
3	3/30/2021	<<<<<	A Value in R	_	960,000	_	240,000	_
4
Sheet8

Cell Formulas
Range	Formula
P3	P3	=IF(R3=0,0,INDEX(S2:X2,MATCH(TRUE,ISNUMBER(S3:X3),0)))
Press CTRL+SHIFT+ENTER to enter array formulas.

With the formula typed in the Formula Bar and your cursor still in the Formula bar, hold down the Ctrl and Shift keys at the same time as you hit Return.
That will then enclose the formula in curly braces. { } and create an Array Formula. (Do not just try and type the Curly Braces). If you edit the formula you will need to always re-enter with Ctrl Shift Enter.