Check if a row contains a negative value, if so, insert that cells first row in specific column-CodePudding

I am trying to figure out something I am not sure can be done with formula's alone (hence the tag of VBA as I am open to VBA-related answers).

As you can see in the screenshot below, I have a column I, in cell I2 (and from I2 onwards, I3, I4 etc) I want to check that cells row (I2 -> J2, K2, L2 etc) and when I find the first negative value, the formula inserts that specific cells first row in that column, in this case, the date of that column.

I am currently using the following formula: =IF(COUNTIF(J2:BJ2,"<0"),"Yes","No")

However, I was not sure how to insert the first negative values date, as you can seen in the next screenshot.

I would like my data to look like the following:

Anyone got any ideas? or is this a VBA task?

CodePudding user response：

INDEX/MATCH will do the trick. Wrap it in IFERROR to return the "No" result.

INDEX($J$1:$M$1,MATCH(TRUE,INDEX(J2:M2<0,),0))