Find number in a list and return number after it.

[dwrowe001]

Hi everyone,
I have a worksheet where I have a formula looking for target numbers (in B column) from the Master sheet, and then returning the number right above it or just before it. It does this for all the instances of that target number found in the B column of Master list.

Here is screenshot of Sheet1

B column only contains the target number, 1 to 10, no formulas
C column contains formula: COUNTIF(B1Table,B3), copied down C3 to C12. B1table is the B column of Master sheet.
D3 thru XX Columns contain the formula, copied across and down:
IF($C3>=COLUMNS($F3:F3),SUMPRODUCT(--(LARGE((Master!$B$2:Master!$B$1999=$B3)*ROW(Master!$B$2:Master!$B$1999),$C3-COLUMNS($F3:F3)+1)>ROW(Master!$B$2:Master!$B$1999)),--(ROW(Master!$B$2:Master!$B$1999)>=LARGE((Master!$B$2:Master!$B$1999=$B3)*ROW(Master!$B$2:Master!$B$1999),$C3-COLUMNS($F3:F3)+1)-$B1),Master!$B$2:Master!$B$1999),"0")

for reference here is what formula in V12 looks like:
=IF($C12>=COLUMNS($D12:V12),SUMPRODUCT(--(LARGE((Master!$B$3:Master!$B$2000=$B12)*ROW(Master!$B$3:Master!$B$2000),$C12-COLUMNS($D12:V12)+1)>ROW(Master!$B$3:Master!$B$2000)),--(ROW(Master!$B$3:Master!$B$2000)>=LARGE((Master!$B$3:Master!$B$2000=$B12)*ROW(Master!$B$3:Master!$B$2000),$C12-COLUMNS($D12:V12)+1)-$B$1),Master!$B$3:Master!$B$2000),"0")

Here a screenshot of Master sheet:

I'm trying to come up with a similar formula, that does the same thing, but instead of returning the number directly above the target number it returns the number directly below it. Make it Sheet2.
I've tried and tried to modify the above formula to get it to do that, but I just mess things up. I can't do it. Can someone help me figure this out.

It would be awesome if you could also explain how the above formula works.. I can't understand how it all works together to accomplish what it does.

Thank you so much in advance for your help.

Dave

 

[Fluff]

What version of Excel are you using?
Please update you account details to show this, as it affects which functions you can use.

 

[dwrowe001]

What version of Excel are you using?
Please update you account details to show this, as it affects which functions you can use.

Ok, I updated my profile.. think I got everything. I'm using MSOFFICE 2016, Excel 2016. 64bit.

 

[Fluff]

Ok, thanks for that, how about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2		target		One up
3		1		3	7	5	10	9
4		2		4	6	3	7	4	1	9	3	8	10	3	2	3	2	2	7	5
5		3		7	10	3	7	1	6	6	9	8
6		4		3	1	7	9	10	8	7
7		5		10	10	2	2	3	2	7	6	2
8		6		5	1	6	8	4	10	6	8	2	8
9		7		9	9	9	8	9	10	10	2
10		8		5	2	2	2	2	8	5	2	5	5	10
11		9		0	9	10	3	6	4	9	10	6	1	4	4
12		10		9	8	4	9	6	5	8	8	5	6	2	2
13
14				One down
15		1		4	6	3	9	2
16		2		8	5	8	5	8	8	8	5	6	10	2	10	2	2	7	5	0
17		3		4	3	9	1	2	5	2	2	2
18		4		10	9	2	6	2	9	9
19		5		8	6	1	10	8	10	8	8	2
20		6		9	6	2	10	9	6	3	3	5	10
21		7		3	3	4	1	2	5	4	2
22		8		10	7	6	10	8	10	4	6	2	6	3
23		9		9	10	7	10	7	9	7	4	7	1	2	3
24		10		5	9	3	5	9	4	1	6	7	2	7	8
25
Sheet1

Cell Formulas
Range	Formula
D3:T12	D3	=IFERROR(INDEX(Master!$B$1:$B$2000,AGGREGATE(15,6,(ROW(Master!$B$2:$B$2000)-ROW(Master!$B$2)+1)/(Master!$B$2:$B$2000=$B3),COLUMNS($D3:D3))),"")
D15:T24	D15	=IFERROR(INDEX(Master!$B$3:$B$2001,AGGREGATE(15,6,(ROW(Master!$B$2:$B$2000)-ROW(Master!$B$2)+1)/(Master!$B$2:$B$2000=$B15),COLUMNS($D15:D15))),"")

+Fluff New.xlsm
	B
1
2	9
3	9
4	10
5	5
6	8
7	10
8	9
9	7
10	3
11	4
12	10
13	3
14	3
15	9
16	10
17	5
18	6
19	9
20	7
21	3
22	1
23	4
24	9
25	9
26	7
27	4
28	2
29	8
30	7
Master

 

[dwrowe001]

Ok, thanks for that, how about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2		target		One up
3		1		3	7	5	10	9
4		2		4	6	3	7	4	1	9	3	8	10	3	2	3	2	2	7	5
5		3		7	10	3	7	1	6	6	9	8
6		4		3	1	7	9	10	8	7
7		5		10	10	2	2	3	2	7	6	2
8		6		5	1	6	8	4	10	6	8	2	8
9		7		9	9	9	8	9	10	10	2
10		8		5	2	2	2	2	8	5	2	5	5	10
11		9		0	9	10	3	6	4	9	10	6	1	4	4
12		10		9	8	4	9	6	5	8	8	5	6	2	2
13
14				One down
15		1		4	6	3	9	2
16		2		8	5	8	5	8	8	8	5	6	10	2	10	2	2	7	5	0
17		3		4	3	9	1	2	5	2	2	2
18		4		10	9	2	6	2	9	9
19		5		8	6	1	10	8	10	8	8	2
20		6		9	6	2	10	9	6	3	3	5	10
21		7		3	3	4	1	2	5	4	2
22		8		10	7	6	10	8	10	4	6	2	6	3
23		9		9	10	7	10	7	9	7	4	7	1	2	3
24		10		5	9	3	5	9	4	1	6	7	2	7	8
25
Sheet1

Cell Formulas
Range	Formula
D3:T12	D3	=IFERROR(INDEX(Master!$B$1:$B$2000,AGGREGATE(15,6,(ROW(Master!$B$2:$B$2000)-ROW(Master!$B$2)+1)/(Master!$B$2:$B$2000=$B3),COLUMNS($D3:D3))),"")
D15:T24	D15	=IFERROR(INDEX(Master!$B$3:$B$2001,AGGREGATE(15,6,(ROW(Master!$B$2:$B$2000)-ROW(Master!$B$2)+1)/(Master!$B$2:$B$2000=$B15),COLUMNS($D15:D15))),"")

+Fluff New.xlsm
	B
1
2	9
3	9
4	10
5	5
6	8
7	10
8	9
9	7
10	3
11	4
12	10
13	3
14	3
15	9
16	10
17	5
18	6
19	9
20	7
21	3
22	1
23	4
24	9
25	9
26	7
27	4
28	2
29	8
30	7
Master

Fluff,
Yes, that works quite nicely... thank you for your help.

Dave

 

[Fluff]

You're welcome & thanks for the feedback.