I think this will be a simple quick fix to the trained eye I am sure- help please

[SMISTRETTA]

I have a bunch of data, see below:
What formula do I write, where:
It looks down column B and recognises that it is a negative number, then returns the corresponding number in column A.
In this example, the answer will be (37,797) so the result will be 66.

I have gotten myself totally confused with index, matches, lookups. I need a fresh pair of eyes, thank you.

45​	-
46​	-
47​	-
48​	-
49​	-
50​	-
51​	-
52​	-
53​	-
54​	-
55​	-
56​	-
57​	-
58​	-
59​	-
60​	-
61​	-
62​	-
63​	-
64​	-
65​	-
66​	(37,797)
67​	-
68​	-
69​	-
70​	(80,827)
71​	(81,080)
72​	(81,334)
73​	(81,588)
74​	(81,844)
75​	(82,100)

 

[Micron]

Wouldn't you just drag something like this down some other column, like C?
IF(B1<0,A1,"")
The desired result you posted doesn't say what's supposed to happen because of the other negative numbers, so an assumption would be that you'd need the formula in every row.

 

[SMISTRETTA]

I need the cell to return just one result. So logically I want it to look down the list in B and at the first point it detects the number (37,797), I want the single cell to tell me, hey I found (37,797) and that is located in row 66. So I know the first output which happens to be (37,797) in this case occurs in row 66. The single-cell I refer to is right at the top of the list.
It's worth mentioning that I want it to ignore the rest of the data below, for it to just return the row number as soon as it hits the first number in the list. Thank make sense?

Thank you

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[SMISTRETTA]

I am using MS365 version 2201, will update now thank you

 

[Fluff]

Thanks for that, how about either of these

+Fluff 1.xlsm
	A	B	C
1
2	45	-	66
3	46	-	66
4	47	-
5	48	-
6	49	-
7	50	-
8	51	-
9	52	-
10	53	-
11	54	-
12	55	-
13	56	-
14	57	-
15	58	-
16	59	-
17	60	-
18	61	-
19	62	-
20	63	-
21	64	-
22	65	-
23	66	-37,797
24	67	-
25	68	-
26	69	-
27	70	-80,827
28	71	-81,080
29	72	-81,334
30	73	-81,588
31	74	-81,844
32	75	-82,100
Primary

Cell Formulas
Range	Formula
C2	C2	=INDEX(A2:A32,XMATCH(-0.00001,B2:B32,-1))
C3	C3	=INDEX(FILTER(A2:A32,B2:B32<0),1)

 

[SMISTRETTA]

Oh my goodness, you are a wonderful person!!!! They both worked an absolute treat!!! Thank you, thank you, thank you. It's been driving me mad all day.

 

[SMISTRETTA]

And let's hope England win at the weekend , last Saturday was tough to watch, why take off Smith when he was doing so well!!

 

[Fluff]

Glad we could help & thanks for the feedback.

why take off Smith when he was doing so well!!

Absolutely no idea. Unless Jones doesn't trust him.

 

[SMISTRETTA]

He needs to start trusting him. We shouldn't have lost that game.