lesliewheeler
New Member
- Joined
- Dec 1, 2016
- Messages
- 32
- Office Version
- 2013
- Platform
- Windows
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
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 |