SOLVED

# What formula to use

Copper Contributor

# What formula to use

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 (Copper Contributor)
Solution

# Re: What formula to use

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))

# Re: What formula to use

Thank you @hans_Vogelaar
1 best response

Accepted Solutions
best response confirmed by J_Pardinas (Copper Contributor)
Solution

# Re: What formula to use

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))