Unsure on the Subject / Indexing?

[ajstole]

Apologies for not knowing what this may be.

I have the following table and am trying to figure out if excel is even capable of indexing some value. Each number on top has 2 categories. If the desired number chosen is 34, the result should be 30 and 40 for example. If 30 is chosen, the 10 and 20 should show.

Enter Desired Number Here:
Category 1 last number to be shown:
Category 2 first number to be shown:
30 32 34 Category 1 Category 2 Category 1 Category 2 Category 1 Category 2 1 20 11 30 21 40 2 18 12 28 22 38 3 16 13 26 23 36 4 14 14 24 24 34 5 12 15 22 25 32 6 10 16 20 26 30 7 8 17 18 27 28 8 6 18 16 28 26 9 4 19 14 29 24 10 2 20 12 30 22 <colgroup><col span="6"></colgroup><tbody> </tbody>

<colgroup><col span="6"></colgroup><tbody>
</tbody>

 

[Peter_SSs]

See if this does what you want. If not, please give more explanation/examples.

Excel Workbook
	A	B	C	D	E	F
1	Desired Num	34
2	Cat 1 Last	30
3	Cat 2 First	40
4
5	30		32		34
6	Category 1	Category 2	Category 1	Category 2	Category 1	Category 2
7	1	20	11	30	21	40
8	2	18	12	28	22	38
9	3	16	13	26	23	36
10	4	14	14	24	24	34
11	5	12	15	22	25	32
12	6	10	16	20	26	30
13	7	8	17	18	27	28
14	8	6	18	16	28	26
15	9	4	19	14	29	24
16	10	2	20	12	30	22
Last first

 

[ajstole]

This is awesome!! That was quick, did not expect that at all! If I did add a second column, what about this? Forgive me, I do not how to attach the picture here.

Desired Number
Cat 1 + Cat 2 Last
Cat 3 First
30	32	34
Cat 1	Cat 2	Cat 3	Cat 1	Cat 2	Cat 3	Cat 1	Cat 2	Cat 3
1	11	10	2	20	20	3	27	30
2	12	9	4	18	19	5	24	29
3	13	8	6	16	18	7	21	28
4	14	7	8	14	17	9	18	27
5	15	6	10	12	16	11	15	26
6	16	5	12	10	15	13	12	25
7	17	4	14	8	14	15	9	24
8	18	3	16	6	13	17	6	23
9	19	2	18	4	12	19	3	22
10	20	1	20	2	11	21	0	21

<colgroup><col span="9"></colgroup><tbody>
</tbody>

See if this does what you want. If not, please give more explanation/examples.

Last first

	A	B	C	D	E	F
1	Desired Num	34
2	Cat 1 Last	30
3	Cat 2 First	40
4
5	30		32		34
6	Category 1	Category 2	Category 1	Category 2	Category 1	Category 2
7	1	20	11	30	21	40
8	2	18	12	28	22	38
9	3	16	13	26	23	36
10	4	14	14	24	24	34
11	5	12	15	22	25	32
12	6	10	16	20	26	30
13	7	8	17	18	27	28
14	8	6	18	16	28	26
15	9	4	19	14	29	24
16	10	2	20	12	30	22

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:95px;"><col style="width:82px;"><col style="width:82px;"><col style="width:82px;"><col style="width:82px;"><col style="width:82px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =INDEX(A16:F16,MATCH(B1,A5:E5,0)) B3 =INDEX(A7:F7,MATCH(B1,A5:E5,0)+1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

This is awesome!! That was quick, did not expect that at all! If I did add a second column, what about this? Forgive me, I do not how to attach the picture here.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Desired Num	32
2	Cat 1 Last	20
3	Cat 2 First	30
4	Cat 3 First	20
5	30			32			34
6	Category 1	Category 2	Category 3	Category 1	Category 2	Category 3	Category 1	Category 2	Category 3
7	1	20		11	30	20	21	40
8	2	18		12	28		22	38
9	3	16		13	26		23	36
10	4	14		14	24		24	34
11	5	12		15	22		25	32
12	6	10		16	20		26	30
13	7	8		17	18		27	28
14	8	6		18	16		28	26
15	9	4		19	14		29	24
16	10	2		20	12		30	22
Last first

BTW
1. Best not to fully quote long posts as it makes the thread harder to read/navigate and just occupies storage space needlessly. If you want to quote, quote small, relevant parts only.
2. Be patient when you submit a post. Especially for new members they can get help up for a while. That should improve as you have been here longer and post more.
3. See my signature block below for a link of how to post small screen shots.

 

[Aladin Akyurek]

Hard to follow...

You have:

Row\Col	A​	B​	C​	D​	E​	F​
1​	Enter Desired Number Here:
2​	Category 1 last number to be shown:
3​	Category 2 first number to be shown:
4​	30​		32​		34​
5​	Category 1	Category 2	Category 1	Category 2	Category 1	Category 2
6​	1​	20​	11​	30​	21​	40​
7​	2​	18​	12​	28​	22​	38​
8​	3​	16​	13​	26​	23​	36​
9​	4​	14​	14​	24​	24​	34​
10​	5​	12​	15​	22​	25​	32​
11​	6​	10​	16​	20​	26​	30​
12​	7​	8​	17​	18​	27​	28​
13​	8​	6​	18​	16​	28​	26​
14​	9​	4​	19​	14​	29​	24​
15​	10​	2​	20​	12​	30​	22​

E1:E3 are unfilled. At least E1 should be given, right?

What row 4 means is unclear.