Need formula to reference the cell in row above value that appears multiple times

[dantheman82]

Can somebody give me a formula to pull a list of values by looking at the code that I have attached? The code below has a list of fruits with a number on the row above every fruit. I need the formula to pull the number on the row above "Strawberry" every time it appears on this spreadsheet. Thanks!

25
Banana

34
Apple

9
Plum

17
Pear

29
Strawberry

20
Blueberry

92
Orange

56
Plum

23
Apple

43
Orange

90
Pear

35
Strawberry

23
Orange

78
Strawberry

72
Apple

20
Orange

57
Banana

88
Strawberry

96
Tangerine

58
Plum

74
Apple

83
Strawberry

50
Pineapple

2
Orange

8
Pear

5
Strawberry

11
Apple

 

[arthur_w]

Well this is going to be time consuming but:
=if('Your cell'="banana"; 25;
if('Your cell'="Apple"; 34;
if('Your cell'="Plum";9;
...
if('Your cell'="Apple";11;"Error"))))))))))

Substitute 'Your cell' by your cell, and fill in the rest of the values in "...", at every 'if' i created a new line so it looks more organized, you could type without it like =if(a;b;if(a;b;if(a;b;c)))

 

[Habtest]

Welcome to the board, please update your office version in account info because the best solution depends on that.

See if it works:

Book1
	A	B
1	25	Strawberry
2	Banana	29
3		35
4	34	78
5	Apple	88
6		83
7	9	5
8	Plum
9
10	17
11	Pear
12
13	29
14	Strawberry
15
16	20
17	Blueberry
18
19	92
20	Orange
21
22	56
23	Plum
24
25	23
26	Apple
27
28	43
29	Orange
30
31	90
32	Pear
33
34	35
35	Strawberry
36
37	23
38	Orange
39
40	78
41	Strawberry
42
43	72
44	Apple
45
46	20
47	Orange
48
49	57
50	Banana
51
52	88
53	Strawberry
54
55	96
56	Tangerine
57
58	58
59	Plum
60
61	74
62	Apple
63
64	83
65	Strawberry
66
67	50
68	Pineapple
69
70	2
71	Orange
72
73	8
74	Pear
75
76	5
77	Strawberry
78
79	11
80	Apple
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=IFERROR(INDEX($A$1:$A$80,AGGREGATE(15,6,ROW($A$1:$A$80)/($A$1:$A$80=$B$1),COUNTA($B$1:B1))-1),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A81	Expression	=A2="Strawberry"	text	NO

 

[Fluff]

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

Another option if you have 365

+Fluff 1.xlsm
	A	B
1		Strawberry
2	25	29
3	Banana	35
4		78
5	34	88
6	Apple	83
7		5
8	9
9	Plum
10
11	17
12	Pear
13
14	29
15	Strawberry
16
17	20
18	Blueberry
19
20	92
21	Orange
22
23	56
24	Plum
25
26	23
27	Apple
28
29	43
30	Orange
31
32	90
33	Pear
34
35	35
36	Strawberry
37
38	23
39	Orange
40
41	78
42	Strawberry
43
44	72
45	Apple
46
47	20
48	Orange
49
50	57
51	Banana
52
53	88
54	Strawberry
55
56	96
57	Tangerine
58
59	58
60	Plum
61
62	74
63	Apple
64
65	83
66	Strawberry
67
68	50
69	Pineapple
70
71	2
72	Orange
73
74	8
75	Pear
76
77	5
78	Strawberry
79
80	11
81	Apple
Master

Cell Formulas
Range	Formula
B2:B7	B2	=FILTER(A2:A101,A3:A102=B1)
Dynamic array formulas.