SOLVED

What formula to use

New Contributor

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 Replies
best response confirmed by J_Pardinas (New Contributor)
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))

Thank you @hans_Vogelaar