Need formula to solve a 3 Criteria Lookup

Hi Experts,

I was hoping to get some formula help with a complex lookup I'm struggling with.

My data is structured as follows:
* I have part numbers listed in Column A
* I have month/year across in Row 1
* For each part number, I have the stock quantity of that part for each month

This table is a simplified view of what my data looks like

Part # Sep-21 Oct-21 Nov-21 Dec-21 Jan-22 Feb-22
Part 1 5 10 15 -7
Part 2 100 200 -50
Part 3 15 25 75 100 -50

I need a formula that does the following:
1. I need to do a lookup down Column A for a particular part number
2. In the row where the part number is found, I need to look across that row and see when the quantity goes negative
3. Once I find the negative quantity, I need to grab the date at the top of that column

So, using my example, this formula would produce the following results:
Part 1 ==> Dec-21
Part 2 ==> Nov-21
Part 3 ==> Jan-22

I hope my explanation is clear. Any help that you can offer would be appreciated. Thanks in advance.

TMc

Also, are you still on Ver. 2010? If not, please update it.

I am using Office 365.

B8=aggregate(15,6,b1:g1/(a2:a4=b7)/(b2:g4<0),1)

Per Alan's request, I've attached an example. Apologies, I should have added the attachment with the original post.

A1:G6 is a matrix of part numbers and their stock quantities for each month represented in Row 1
A10:F11 shows the results I am looking for. For example, the stock quantity of Part #1 goes negative in Feb-22 and Oct-21 for Part #2.

Geoff, your formula works perfectly! Thanks for the quick response.

B11=IFERROR(AGGREGATE(15,6,$B$1:$G$1/($A$2:$A$6=B$10)/($B$2:$G$6<0),1),"")

Copy across