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

dantheman82

New Member
Joined
Apr 30, 2017
Messages
1
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!
Excel Formula:
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

arthur_w

New Member
Joined
May 12, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 30, 2020
Messages
194
Office Version
  1. 365
Platform
  1. Windows
Welcome to the board, please update your office version in account info because the best solution depends on that.

See if it works:
Book1
AB
125Strawberry
2Banana29
335
43478
5Apple88
683
795
8Plum 
9 
1017
11Pear
12
1329
14Strawberry
15
1620
17Blueberry
18
1992
20Orange
21
2256
23Plum
24
2523
26Apple
27
2843
29Orange
30
3190
32Pear
33
3435
35Strawberry
36
3723
38Orange
39
4078
41Strawberry
42
4372
44Apple
45
4620
47Orange
48
4957
50Banana
51
5288
53Strawberry
54
5596
56Tangerine
57
5858
59Plum
60
6174
62Apple
63
6483
65Strawberry
66
6750
68Pineapple
69
702
71Orange
72
738
74Pear
75
765
77Strawberry
78
7911
80Apple
Sheet1
Cell Formulas
RangeFormula
B2:B9B2=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
CellConditionCell FormatStop If True
A1:A81Expression=A2="Strawberry"textNO
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
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
AB
1Strawberry
22529
3Banana35
478
53488
6Apple83
75
89
9Plum
10
1117
12Pear
13
1429
15Strawberry
16
1720
18Blueberry
19
2092
21Orange
22
2356
24Plum
25
2623
27Apple
28
2943
30Orange
31
3290
33Pear
34
3535
36Strawberry
37
3823
39Orange
40
4178
42Strawberry
43
4472
45Apple
46
4720
48Orange
49
5057
51Banana
52
5388
54Strawberry
55
5696
57Tangerine
58
5958
60Plum
61
6274
63Apple
64
6583
66Strawberry
67
6850
69Pineapple
70
712
72Orange
73
748
75Pear
76
775
78Strawberry
79
8011
81Apple
Master
Cell Formulas
RangeFormula
B2:B7B2=FILTER(A2:A101,A3:A102=B1)
Dynamic array formulas.
 

Forum statistics

Threads
1,141,818
Messages
5,708,769
Members
421,589
Latest member
b_gernert

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top