Return Col A if equal to Col D = Col B in columns E:I

[Norm45]

Can anybody help with the above problem hopefully explained in enclosed sheet

Number	Sk		Sk	R1	R2	R3	R4	R5
1	19		1	4	14	25	27	37
2	5		2	3	9	17	29	34
3	2		3	7	13	28	38
4	1		4	30	32
5	9		5	2	26	31
6	23		6	22	24	39
7	3		7	11	19
8	8		8	8
9	2		9	5	15
10	15		10	18
11	7		11	12	21
12	11		12	23
13	3		13	36
14	1		14	20
15	9		15	10	33
16	16		16	33
17	2		17
18	10		18	35
19	7		19	1
20	14		20
21	11		>20	6
22	6
23	12
24	6
25	1
26	5
27	1
28	3
29	2
30	4
31	5
32	4
33	15
34	2
35	18
36	13
37	1
38	3
39	6

Thanks Norm45

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you mean?
My formula disagrees with your results in one cell (green). I'm hoping that was just a typo on your part?

22 10 08.xlsm
	A	B	C	D	E	F	G	H	I
1	Number	Sk		Sk	R1	R2	R3	R4	R5
2	1	19		1	4	14	25	27	37
3	2	5		2	3	9	17	29	34
4	3	2		3	7	13	28	38
5	4	1		4	30	32
6	5	9		5	2	26	31
7	6	23		6	22	24	39
8	7	3		7	11	19
9	8	8		8	8
10	9	2		9	5	15
11	10	15		10	18
12	11	7		11	12	21
13	12	11		12	23
14	13	3		13	36
15	14	1		14	20
16	15	9		15	10	33
17	16	16		16	16
18	17	2		17
19	18	10		18	35
20	19	7		19	1
21	20	14		20
22	21	11		>20	6
23	22	6
24	23	12
25	24	6
26	25	1
27	26	5
28	27	1
29	28	3
30	29	2
31	30	4
32	31	5
33	32	4
34	33	15
35	34	2
36	35	18
37	36	13
38	37	1
39	38	3
40	39	6
Norm45

Cell Formulas
Range	Formula
E2:I22	E2	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$40)/IF(ISNUMBER($D2),$B$2:$B$40=$D2,$B$2:$B$40>$D1),COLUMNS($E:E))),"")

 

[Norm45]

Welcome to the MrExcel board!

Is this what you mean?
My formula disagrees with your results in one cell (green). I'm hoping that was just a typo on your part?

22 10 08.xlsm
	A	B	C	D	E	F	G	H	I
1	Number	Sk		Sk	R1	R2	R3	R4	R5
2	1	19		1	4	14	25	27	37
3	2	5		2	3	9	17	29	34
4	3	2		3	7	13	28	38
5	4	1		4	30	32
6	5	9		5	2	26	31
7	6	23		6	22	24	39
8	7	3		7	11	19
9	8	8		8	8
10	9	2		9	5	15
11	10	15		10	18
12	11	7		11	12	21
13	12	11		12	23
14	13	3		13	36
15	14	1		14	20
16	15	9		15	10	33
17	16	16		16	16
18	17	2		17
19	18	10		18	35
20	19	7		19	1
21	20	14		20
22	21	11		>20	6
23	22	6
24	23	12
25	24	6
26	25	1
27	26	5
28	27	1
29	28	3
30	29	2
31	30	4
32	31	5
33	32	4
34	33	15
35	34	2
36	35	18
37	36	13
38	37	1
39	38	3
40	39	6
Norm45

Cell Formulas
Range	Formula
E2:I22	E2	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$40)/IF(ISNUMBER($D2),$B$2:$B$40=$D2,$B$2:$B$40>$D1),COLUMNS($E:E))),"")

Hi Peter,

Thanks for the quick solution and you're right it was a typo on my part

Rgds

Norm45

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.