Excel query

[rival36]

I have data in green table. I want to create logic in second table,that, if I enter name of student, system should find his marks in green table according to subjects and display it in orange cells. If name of student repeats, system should give repeated results as shown in "required result". Also system should give average of all marks in particular subject. thanks

mini sheet attached

test sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2
3													Average	65	29	34	30
4		Sr.No.	Name	Test	Math	Science	Arts	Hindi				enter student name	Sagar	arts	hindi	science	math
5		1	Sagar	1	54	65	98	8					1	98	8	65	54
6		2	Toran	1	25	33	66	85					2	32	12	12	22
7		3	To****a	1	22	55	44	6					3	65	66	26	13
8		4	Maya	1	77	8	5	65
9		5	Sagar	2	22	12	32	12				required result
10		6	Sagar	3	13	26	65	66
11		7	Maya	2	78	56	32	54
12		8	To****a	2	15	54	78	54
13
14
15
16						I have data in green table. I want to create logic in second table,that, if I enter name of student, system should find his marks in green table according to subjects and display it in orange cells. If name of student repeats, system should give repeated results as shown in "required result". Also system should give average of all marks in particular subject. thanks
17
18
19
20
21
22
23
Sheet1

 

[Legacy 143009]

Paste this to N5 and drag right, pull dow:n

=INDEX($E$5:$H$12,MATCH(1,($C$5:$C$12=$M$4)*($D$5:$D$12=$M5),0),MATCH(N$4,$E$4:$H$4,0))

This is an array formula. Only pasting is not enough. Press Ctrl+Shift+Enter together after paste.

 

[rival36]

worked perfectly. U r genius man. tons of thanks

 

[Legacy 143009]

Glad it did work! Thanks for the feedback

 

[Fluff]

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.
This is the same question as you asked in your previous thread, where you have a non-array formula solution.