Lookup nth column value for specified 'n'

[Ian McPherson]

Attached example shows a table of % values. For a specified n=C4 value, lookup up the corresponding %'s from 1-n (yellow highlight).
Thanking you in advance.

Example.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
4	Select	21	%				1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22
5		1	14.0%			1	100.0%	65.0%	50.0%	34.0%	31.0%	25.0%	23.0%	21.0%	20.5%	20.0%	19.0%	18.0%	18.0%	17.0%	17.0%	16.0%	16.0%	15.0%	15.0%	14.0%	14.0%	13.0%
6		2	10.0%			2		35.0%	30.0%	28.0%	24.0%	20.0%	19.0%	18.0%	17.0%	17.0%	16.0%	15.0%	15.0%	14.0%	13.0%	12.0%	12.0%	11.0%	11.0%	10.0%	10.0%	9.0%
7		3	8.0%			3			20.0%	22.0%	18.0%	17.5%	16.5%	15.0%	14.5%	14.0%	13.0%	13.0%	12.0%	12.0%	10.0%	9.0%	9.0%	9.0%	8.5%	8.5%	8.0%	7.8%
8		4	6.6%			4				16.0%	15.0%	15.0%	14.0%	13.0%	12.0%	12.0%	11.0%	11.0%	10.0%	9.0%	8.0%	7.5%	7.0%	7.0%	7.0%	6.7%	6.6%	6.5%
9		5	6.0%			5					12.0%	12.5%	11.5%	11.0%	10.0%	10.0%	9.5%	9.5%	8.0%	8.0%	7.0%	7.0%	6.5%	6.5%	6.3%	6.2%	6.0%	6.0%
10		6	5.5%			6						10.0%	9.0%	9.0%	8.0%	8.0%	8.0%	8.0%	7.0%	7.0%	6.4%	6.5%	6.0%	6.0%	5.8%	5.7%	5.5%	5.5%
11		7	5.0%			7							7.0%	7.0%	7.0%	5.5%	6.5%	6.5%	6.0%	5.8%	5.9%	6.0%	5.5%	5.5%	5.3%	5.2%	5.0%	5.0%
12		8	4.6%			8								6.0%	6.0%	5.0%	5.5%	5.0%	5.0%	4.9%	5.4%	5.5%	5.0%	5.0%	4.9%	4.7%	4.6%	4.6%
13		9	4.2%			9									5.0%	4.5%	4.5%	4.0%	4.6%	4.5%	5.0%	5.0%	4.7%	4.6%	4.5%	4.4%	4.2%	4.2%
14		10	3.9%			10										4.0%	3.8%	3.6%	4.2%	4.2%	4.6%	4.6%	4.4%	4.3%	4.1%	4.1%	3.9%	3.9%
15		11	3.6%			11											3.2%	3.3%	3.8%	3.9%	4.2%	4.3%	4.2%	4.0%	3.8%	3.8%	3.6%	3.6%
16		12	3.4%			12												3.1%	3.4%	3.5%	3.8%	3.9%	3.9%	3.7%	3.5%	3.6%	3.4%	3.4%
17		13	3.2%			13													3.0%	3.2%	3.5%	3.6%	3.6%	3.4%	3.2%	3.4%	3.2%	3.2%
18		14	3.1%			14														3.0%	3.2%	3.3%	3.3%	3.2%	3.1%	3.2%	3.1%	3.1%
19		15	3.0%			15															3.0%	3.0%	3.1%	3.1%	3.0%	3.0%	3.0%	3.0%
20		16	2.9%			16																2.8%	3.0%	3.0%	2.9%	2.9%	2.9%	2.9%
21		17	2.8%			17																	2.8%	2.9%	2.8%	2.8%	2.8%	2.8%
22		18	2.7%			18																		2.8%	2.7%	2.7%	2.7%	2.7%
23		19	2.6%			19																			2.6%	2.6%	2.6%	2.6%
24		20	2.5%			20																				2.5%	2.5%	2.5%
25		21	2.4%			21																					2.4%	2.4%
26						22																						2.3%
Sheet1

Cell Formulas
Range	Formula
C5:C25	C5	=FILTER(G5:G26,(G5:G26<=C4))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C4	List	=$H$4:$AC$4

 

[bebo021999]

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
4	Select	21	%				1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22
5		1	14%			1	1	0.65	0.5	0.34	0.31	0.25	0.23	0.21	0.205	0.2	0.19	0.18	0.18	0.17	0.17	0.16	0.16	0.15	0.15	0.14	0.14	0.13
6		2	10%			2		0.35	0.3	0.28	0.24	0.2	0.19	0.18	0.17	0.17	0.16	0.15	0.15	0.14	0.13	0.12	0.12	0.11	0.11	0.1	0.1	0.09
7		3	8%			3			0.2	0.22	0.18	0.175	0.165	0.15	0.145	0.14	0.13	0.13	0.12	0.12	0.1	0.09	0.09	0.09	0.085	0.085	0.08	0.078
8		4	7%			4				0.16	0.15	0.15	0.14	0.13	0.12	0.12	0.11	0.11	0.1	0.09	0.08	0.075	0.07	0.07	0.07	0.067	0.066	0.065
9		5	6%			5					0.12	0.125	0.115	0.11	0.1	0.1	0.095	0.095	0.08	0.08	0.07	0.07	0.065	0.065	0.063	0.062	0.06	0.06
10		6	6%			6						0.1	0.09	0.09	0.08	0.08	0.08	0.08	0.07	0.07	0.064	0.065	0.06	0.06	0.058	0.057	0.055	0.055
11		7	5%			7							0.07	0.07	0.07	0.055	0.065	0.065	0.06	0.058	0.059	0.06	0.055	0.055	0.053	0.052	0.05	0.05
12		8	5%			8								0.06	0.06	0.05	0.055	0.05	0.05	0.049	0.054	0.055	0.05	0.05	0.049	0.047	0.046	0.046
13		9	4%			9									0.05	0.045	0.045	0.04	0.046	0.045	0.05	0.05	0.047	0.046	0.045	0.044	0.042	0.042
14		10	4%			10										0.04	0.038	0.036	0.042	0.042	0.046	0.046	0.044	0.043	0.041	0.041	0.039	0.039
15		11	4%			11											0.032	0.033	0.038	0.039	0.042	0.043	0.042	0.04	0.038	0.038	0.036	0.036
16		12	3%			12												0.031	0.034	0.035	0.038	0.039	0.039	0.037	0.035	0.036	0.034	0.034
17		13	3%			13													0.03	0.032	0.035	0.036	0.036	0.034	0.032	0.034	0.032	0.032
18		14	3%			14														0.03	0.032	0.033	0.033	0.032	0.031	0.032	0.031	0.031
19		15	3%			15															0.03	0.03	0.031	0.031	0.03	0.03	0.03	0.03
20		16	3%			16																0.028	0.03	0.03	0.029	0.029	0.029	0.029
21		17	3%			17																	0.028	0.029	0.028	0.028	0.028	0.028
22		18	3%			18																		0.028	0.027	0.027	0.027	0.027
23		19	3%			19																			0.026	0.026	0.026	0.026
24		20	3%			20																				0.025	0.025	0.025
25		21	2%			21																					0.024	0.024
26						22																						0.023
Sheet4

Cell Formulas
Range	Formula
C5:C26	C5	=IF(G5<=$C$4,G5,"")
D5:D26	D5	=IF(C5="","",INDEX(H5:AC5,$C$4))

 

[Peter_SSs]

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

Is this what you mean?

22 08 09.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
4	Select	5	%				1	2	3	4	5	6	7	8
5			31.0%			1	100.0%	65.0%	50.0%	34.0%	31.0%	25.0%	23.0%	21.0%
6			24.0%			2		35.0%	30.0%	28.0%	24.0%	20.0%	19.0%	18.0%
7			18.0%			3			20.0%	22.0%	18.0%	17.5%	16.5%	15.0%
8			15.0%			4				16.0%	15.0%	15.0%	14.0%	13.0%
9			12.0%			5					12.0%	12.5%	11.5%	11.0%
10						6						10.0%	9.0%	9.0%
11						7							7.0%	7.0%
12						8								6.0%
13						9
Sheet2 (2)

Cell Formulas
Range	Formula
D5:D9	D5	=LET(col,FILTER(H5:AC26,H4:AC4=C4),FILTER(col,col<>""))
Dynamic array formulas.

 

[Ian McPherson]

Thank-you, appreciate the prompt reply.

 

[Ian McPherson]

Appreciate the prompt reply.
Where is the 'Mark as solution' button??

 

[Peter_SSs]

Where is the 'Mark as solution' button??

Top right of the relevant post

 

[Ian McPherson]

Thks, can't see for looking!

 

[Peter_SSs]

Thks, can't see for looking!

Given that you marked post #2 it appears that you wanted both the sequence of numbers in column C and the percentages in column C whereas I thought you might have only wanted the percentages.

If you are interested, you can get both with a single formula in a single cell without copying it down or across at all - assuming your headings are actually as shown in H4:AC4**.
If you want to try it, clear all the formulas from C5:D26 and just put this formula in C5.

22 08 09.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
4	Select	5	%				1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22
5		1	31.0%			1	100.0%	65.0%	50.0%	34.0%	31.0%	25.0%	23.0%	21.0%	20.5%	20.0%	19.0%	18.0%	18.0%	17.0%	17.0%	16.0%	16.0%	15.0%	15.0%	14.0%	14.0%	13.0%
6		2	24.0%			2		35.0%	30.0%	28.0%	24.0%	20.0%	19.0%	18.0%	17.0%	17.0%	16.0%	15.0%	15.0%	14.0%	13.0%	12.0%	12.0%	11.0%	11.0%	10.0%	10.0%	9.0%
7		3	18.0%			3			20.0%	22.0%	18.0%	17.5%	16.5%	15.0%	14.5%	14.0%	13.0%	13.0%	12.0%	12.0%	10.0%	9.0%	9.0%	9.0%	8.5%	8.5%	8.0%	7.8%
8		4	15.0%			4				16.0%	15.0%	15.0%	14.0%	13.0%	12.0%	12.0%	11.0%	11.0%	10.0%	9.0%	8.0%	7.5%	7.0%	7.0%	7.0%	6.7%	6.6%	6.5%
9		5	12.0%			5					12.0%	12.5%	11.5%	11.0%	10.0%	10.0%	9.5%	9.5%	8.0%	8.0%	7.0%	7.0%	6.5%	6.5%	6.3%	6.2%	6.0%	6.0%
10						6						10.0%	9.0%	9.0%	8.0%	8.0%	8.0%	8.0%	7.0%	7.0%	6.4%	6.5%	6.0%	6.0%	5.8%	5.7%	5.5%	5.5%
11						7							7.0%	7.0%	7.0%	5.5%	6.5%	6.5%	6.0%	5.8%	5.9%	6.0%	5.5%	5.5%	5.3%	5.2%	5.0%	5.0%
12						8								6.0%	6.0%	5.0%	5.5%	5.0%	5.0%	4.9%	5.4%	5.5%	5.0%	5.0%	4.9%	4.7%	4.6%	4.6%
13						9									5.0%	4.5%	4.5%	4.0%	4.6%	4.5%	5.0%	5.0%	4.7%	4.6%	4.5%	4.4%	4.2%	4.2%
14						10										4.0%	3.8%	3.6%	4.2%	4.2%	4.6%	4.6%	4.4%	4.3%	4.1%	4.1%	3.9%	3.9%
15						11											3.2%	3.3%	3.8%	3.9%	4.2%	4.3%	4.2%	4.0%	3.8%	3.8%	3.6%	3.6%
16						12												3.1%	3.4%	3.5%	3.8%	3.9%	3.9%	3.7%	3.5%	3.6%	3.4%	3.4%
17						13													3.0%	3.2%	3.5%	3.6%	3.6%	3.4%	3.2%	3.4%	3.2%	3.2%
18						14														3.0%	3.2%	3.3%	3.3%	3.2%	3.1%	3.2%	3.1%	3.1%
19						15															3.0%	3.0%	3.1%	3.1%	3.0%	3.0%	3.0%	3.0%
20						16																2.8%	3.0%	3.0%	2.9%	2.9%	2.9%	2.9%
21						17																	2.8%	2.9%	2.8%	2.8%	2.8%	2.8%
22						18																		2.8%	2.7%	2.7%	2.7%	2.7%
23						19																			2.6%	2.6%	2.6%	2.6%
24						20																				2.5%	2.5%	2.5%
25						21																					2.4%	2.4%
26						22																						2.3%
Extract Col (2)

Cell Formulas
Range	Formula
C5:D9	C5	=LET(s,SEQUENCE(C4),CHOOSE({1,2},s,INDEX(H5:AC26,s,C4)))
Dynamic array formulas.

** If the H4:AC4 headings are in fact different, a slight modification would be needed but still just the single formula can do it.

 

[Ian McPherson]

Hey, that's really neat...cheers!

 

[Peter_SSs]

You're welcome.

BTW, I have removed the 'Mark as solution' from post #9. You can place it elsewhere.
Remember ...

Top right of the relevant post