Formula to List all numbers that appear 1 row below search target number

[lesliewheeler]

Hi Friends,

I am looking for help with a formula basis the below condition:-

1. We have a list in column A approx 10000 row of numbers between 0 and 36 in a random manner
2. I would like to identify and list down all numbers that appear 1 row below our input/search target number, for e,g if our search criteria is 1 then the formula check the vertical list where 1 appears and returns the value of the cell one row below the target number. This is done for however many time the search number is appearing in the list. which means that if 1 is identified 500 times in the the list it will return all number which appeared below the number 1.
3. the desired search number can be entered in a cell

I have attached the sample excel with desired results for reference

test example.xlsx
	C	D	E	F
1	List of Numbers	Search Number	Result
2	12	1	25
3	6		22
4	5		21
5	27		28
6	1		5
7	25
8	33
9	30
10	3
11	7
12	9
13	23
14	35
15	9
16	15
17	29
18	17
19	31
20	36
21	15
22	30
23	30
24	15
25	33
26	27
27	9
28	17
29	7
30	27
31	15
32	11
33	2
34	34
35	35
36	8
37	17
38	32
39	14
40	33
41	10
42	6
43	34
44	13
45	31
46	18
47	1
48	22
49	24
50	22
51	13
52	22
53	36
54	25
55	21
56	4
57	29
58	21
59	26
60	34
61	28
62	12
63	36
64	32
65	1
66	21
67	33
68	19
69	22
70	10
71	20
72	21
73	16
74	14
75	19
76	29
77	27
78	22
79	13
80	27
81	36
82	1
83	28
84	35
85	34
86	22
87	33
88	35
89	16
90	16
91	35
92	19
93	6
94	18
95	4
96	16
97	17
98	9
99	7
100	1
101	5
Sheet1

 

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

 

[lesliewheeler]

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

Excel 2013

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C
1	List of Numbers	Search Number	Result
2	12	1	25
3	6		22
4	5		21
5	27		28
6	1		5
7	25
8	33
9	30
10	3
11	7
12	9
13	23
14	35
15	9
16	15
17	29
18	17
19	31
20	36
21	15
22	30
23	30
24	15
25	33
26	27
27	9
28	17
29	7
30	27
31	15
32	11
33	2
34	34
35	35
36	8
37	17
38	32
39	14
40	33
41	10
42	6
43	34
44	13
45	31
46	18
47	1
48	22
49	24
50	22
51	13
52	22
53	36
54	25
55	21
56	4
57	29
58	21
59	26
60	34
61	28
62	12
63	36
64	32
65	1
66	21
67	33
68	19
69	22
70	10
71	20
72	21
73	16
74	14
75	19
76	29
77	27
78	22
79	13
80	27
81	36
82	1
83	28
84	35
85	34
86	22
87	33
88	35
89	16
90	16
91	35
92	19
93	6
94	18
95	4
96	16
97	17
98	9
99	7
100	1
101	5
Test

Cell Formulas
Range	Formula
C2:C15	C2	=IFERROR(INDEX($A$2:$A$101,AGGREGATE(15,6,(ROW($A$2:$A$101)-ROW($A$2)+1)/($A$2:$A$101=$B$2),ROWS(C$2:C2))+1),"")

 

[lesliewheeler]

Ok, how about

+Fluff 1.xlsm
	A	B	C
1	List of Numbers	Search Number	Result
2	12	1	25
3	6		22
4	5		21
5	27		28
6	1		5
7	25
8	33
9	30
10	3
11	7
12	9
13	23
14	35
15	9
16	15
17	29
18	17
19	31
20	36
21	15
22	30
23	30
24	15
25	33
26	27
27	9
28	17
29	7
30	27
31	15
32	11
33	2
34	34
35	35
36	8
37	17
38	32
39	14
40	33
41	10
42	6
43	34
44	13
45	31
46	18
47	1
48	22
49	24
50	22
51	13
52	22
53	36
54	25
55	21
56	4
57	29
58	21
59	26
60	34
61	28
62	12
63	36
64	32
65	1
66	21
67	33
68	19
69	22
70	10
71	20
72	21
73	16
74	14
75	19
76	29
77	27
78	22
79	13
80	27
81	36
82	1
83	28
84	35
85	34
86	22
87	33
88	35
89	16
90	16
91	35
92	19
93	6
94	18
95	4
96	16
97	17
98	9
99	7
100	1
101	5
Test

Cell Formulas
Range	Formula
C2:C15	C2	=IFERROR(INDEX($A$2:$A$101,AGGREGATE(15,6,(ROW($A$2:$A$101)-ROW($A$2)+1)/($A$2:$A$101=$B$2),ROWS(C$2:C2))+1),"")

THANKS A MILLION TON FLUFF...worked like a charm....

Appreciate your support!!!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[lesliewheeler]

You're welcome & thanks for the feedback.

Hi Fluff,
Is there anyway a VBA code can be generated for the above formula...the excel has become very heavy and is taking ages to calculate on a data value change..

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long
   
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B2" Then
      Ary = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value2
      ReDim Nary(1 To UBound(Ary), 1 To 1)
      For r = 1 To UBound(Ary)
         If Ary(r, 1) = Target.Value Then
            nr = nr + 1
            Nary(nr, 1) = Ary(r + 1, 1)
         End If
      Next r
      Range("c2").Resize(UBound(Nary)).Value = Nary
   End If
End Sub

This needs to go in the relevant sheet module & will trigger when B2 is changed.

 

[lesliewheeler]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long
  
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B2" Then
      Ary = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value2
      ReDim Nary(1 To UBound(Ary), 1 To 1)
      For r = 1 To UBound(Ary)
         If Ary(r, 1) = Target.Value Then
            nr = nr + 1
            Nary(nr, 1) = Ary(r + 1, 1)
         End If
      Next r
      Range("c2").Resize(UBound(Nary)).Value = Nary
   End If
End Sub

This needs to go in the relevant sheet module & will trigger when B2 is changed.

Thanks a ton Fluff....u have been a such a big help to me..

I sincerely appreciate your prompt response and assistance..

Thank you so much

 

[Fluff]

My pleasure.