What formula to use

[J_Pardinas]

Hi, I have column A which contains month end dates in ascending order. I have column B with dollar amounts in descending order. I'd like to search for corresponding date when the value in column B becomes <0. What is the correct formula to use? 

[#2 · Accepted solution]

@J_Pardinas 

If you have Excel in Microsoft 365 or Office 2021:

=XLOOKUP(TRUE,B2:B100<0,A2:A100)

Adjust the ranges as needed. If you have an older version, use the following array formula, confirmed with Ctrl+Shift+Enter:

=INDEX(A2:A100,MATCH(TRUE,B2:B100<0,0))

[#3]

Thank you @hans_Vogelaar