Find values in column A & B, using just a row numbers

[motilulla]

Hello,

I have got in column "A" SN and in Column "B" Year (Note Column "B" is formatted As Text)

I want to find values in column A & B, using just a row numbers

For example if I fill only the row number in cell D6 = 15 & in cell D7 =39...

I need a formula which can give me a result in cell F6 = 19 which is from A15 & in the cell G6 = 71/72 which is from B15

Same need a formula which can give me a result in cell F7 = 45 which is from A39 & in the cell G6 = 15/16 which is from B39

Example image is attached for more details

*	A	B	C	D	E	F	G	H	I
1						Result	Result
2						Col A	Col B
3				Find Value		Row	Row
4				Col A & B		Value	Value
5	SN	Year		Of Row Number		SN	Year
6	1	70/71		15		19	71/72
7	2	70/71		39		45	15/16
8	3	70/71
9	4	70/71
10	5	70/71
11	6	70/71
12	16	71/72
13	17	71/72
14	18	71/72
15	19	71/72
16	20	71/72
17	21	71/72
18	22	71/72
19	23	71/72
20	24	71/72
21	25	71/72
22	1	76/77
23	2	76/77
24	3	76/77
25	4	76/77
26	5	76/77
27	6	76/77
28	7	76/77
29	8	76/77
30	9	76/77
31	10	76/77
32	11	76/77
33	39	15/16
34	40	15/16
35	41	15/16
36	42	15/16
37	43	15/16
38	44	15/16
39	45	15/16
40	46	15/16
41	47	15/16
42	48	15/16
43	49	15/16
44
45
46
47

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[jorismoerings]

Hi,

try this
in cell F6

=INDIRECT(ADDRESS(D6;1))

in cell G6

=INDIRECT(ADDRESS(D6;2))

 

[motilulla]

Hi,

try this
in cell F6

=INDIRECT(ADDRESS(D6;1))

in cell G6

=INDIRECT(ADDRESS(D6;2))

jorismoerings, yes it is worked as request. Only in the formula I changed ";" to ","

Just a question INDIRECT is a volatile function does there it could be any non-volatile formula?

Thank you for your help and time.

Good Luck!

Kind Regards,
Moti

 

[Peter_SSs]

As I understand what you want a direct INDEX should do it

21 10 12.xlsm
	A	B	C	D	E	F
6				Row	Col A	Col B
7	1	a		15	18	i
8	2	b		19	30	m
9	3	c
10	4	d
11	5	e
12	6	f
13	16	g
14	17	h
15	18	i
16	19	j
17	20	k
18	23	l
19	30	m
20	32	n
Row

Cell Formulas
Range	Formula
E7:F8	E7	=INDEX(A:A,$D7)

 

[motilulla]

As I understand what you want a direct INDEX should do it

21 10 12.xlsm
	A	B	C	D	E	F
6				Row	Col A	Col B
7	1	a		15	18	i
8	2	b		19	30	m
9	3	c
10	4	d
11	5	e
12	6	f
13	16	g
14	17	h
15	18	i
16	19	j
17	20	k
18	23	l
19	30	m
20	32	n
Row

Cell Formulas
Range	Formula
E7:F8	E7	=INDEX(A:A,$D7)

Peter, ? this is magical formula, I like it very much.

Thank you for your kind help. Have a nice day

Good Luck!

Kind Regards,
Moti

 

[Peter_SSs]

You're welcome.