SOLVED

What formula to use

%3CLINGO-SUB%20id%3D%22lingo-sub-2824603%22%20slang%3D%22en-US%22%3EWhat%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2824603%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20column%20A%20which%20contains%20month%20end%20dates%20in%20ascending%20order.%20I%20have%20column%20B%20with%20dollar%20amounts%20in%20descending%20order.%20I'd%20like%20to%20search%20for%20corresponding%20date%20when%20the%20value%20in%20column%20B%20becomes%20%26lt%3B0.%20What%20is%20the%20correct%20formula%20to%20use%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2824603%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2824631%22%20slang%3D%22en-US%22%3ERe%3A%20What%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2824631%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1179129%22%20target%3D%22_blank%22%3E%40J_Pardinas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20Excel%20in%20Microsoft%20365%20or%20Office%202021%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DXLOOKUP(TRUE%2CB2%3AB100%26lt%3B0%2CA2%3AA100)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdjust%20the%20ranges%20as%20needed.%20If%20you%20have%20an%20older%20version%2C%20use%20the%20following%20array%20formula%2C%20confirmed%20with%20Ctrl%2BShift%2BEnter%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(A2%3AA100%2CMATCH(TRUE%2CB2%3AB100%26lt%3B0%2C0))%3C%2FP%3E%3C%2FLINGO-BODY%3E
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