First Instance of a Negative and Positive Number in Adjacent Columns

[10OAKS]

Hi everyone,

I have 2 columns of numbers I'm trying to write a formula that will point me to the first instance of a negative value in the first column and a positive value on the
same row in the adjacent column.

I've searched the forum and tried combinations of the index and match formulas without success.

Any help would be appreciated.

[Melvinrobb]

Do you want to find the first instance where BOTH of these instances occur (first column is negative AND 2nd column is positive)?

[Tony Valko]

So what result do you want?

Do you want the numbers themselves, the row number or something else?

[10OAKS]

Yes, I need both conditions to be true - negative number in the first column and positive in the second column.

Further, I am looking for the row number - not the values in the cells.

Thanks.

[JosephP]

=MATCH(1,INDEX((B:B<0)*(C:C>0),0),0)
for instance

[Tony Valko]

I am looking for the row number - not the values in the cells.

OK, do you want the row number relative to the data range or do you want the absolute row number?

If this is your data in the range A5:B9...

row 5.....1......1
row 6.....7.....-5
row 7....-2......3
row 8....-3......2
row 9.....6.....-1


The conditions are met on absolute row number 7 or relative row number 3. What result do expect?

[10OAKS]

Thank you JosephP. I tried your formula on my data and it gave me exactly the answer I wanted.

Tony, I wanted the absolute row number and thank you for responding.

[Tony Valko]

Good deal. Thanks for the feedback!