Reference last time job was written down

[jharvey87]

I've attached an example book for reference.

I'm struggling to write a formula that can pull information related to the last time GP was reduced. In the attached workbook, you will see column J represents a month/over month comparison to GP on a job. I'm trying to pull the amount that is last shown as less than $0, and then I can write an index/match formula that can pull the year/month. Any suggestions on which formula to use in order to pull the last time it was negative (Orange highlights are the rows that it should find).

[Bo_Ry]

Please try at
N3
=XLOOKUP(1,($D$7:$D$48=L3)*($J$7:$J$48<0),$B$7:$C$48,,,-1)

P3
=XLOOKUP(1,($D$7:$D$48=L3)*($J$7:$J$48<0),$J$7:$J$48,,,-1)

[jharvey87]

That doesn't work for me. I'm guessing XLookup is a newer function?

[Bo_Ry]

If you really use Office 365, update your office.

If not, try at
N2
=LOOKUP(9^9,B$7:B$48/($D$7:$D$48=$L3)/($J$7:$J$48<0))

P2
=LOOKUP(9^9,$J$7:$J$48/($D$7:$D$48=$L3)/($J$7:$J$48<0))

[jharvey87]

This worked, thank you so much.