need help in a comlex function

[abdul43]

Please help me to write the below function shortly which will be understandable by excel i have 3 sheets among them 2 are of mid and final exam and the third one is of the individual result sheet therefore i wanted that excel pick a student's marks from both sheets by writing only a serial no in the individual sheet for this purpose i gave an if function in every cell of the individual sheet but excel is not accepting the function given by me as well it is to hard and complex to edit the function is..
=IF(AND($G$9=1,2,3,4,5,6,7,8,9,10,11,12,13,14.15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50),'Final11-9th'!D8,'Final11-9th'!D9,'Final11-9th'!D10,'Final11-9th'!D11,'Final11-9th'!D12,'Final11-9th'!D13,'Final11-9th'!D14,'Final11-9th'!D15,'Final11-9th'!D16,'Final11-9th'!D17,'Final11-9th'!D18,'Final11-9th'!D19,'Final11-9th'!D20,'Final11-9th'!D21,'Final11-9th'!D22,'Final11-9th'!D23,'Final11-9th'!D24,'Final11-9th'!D25,'Final11-9th'!D26,'Final11-9th'!D27,'Final11-9th'!D28,'Final11-9th'!D29,'Final11-9th'!D30,'Final11-9th'!D31,'Final11-9th'!D32,'Final11-9th'!D33,'Final11-9th'!D34,'Final11-9th'!D35,'Final11-9th'!D36,'Final11-9th'!D37,'Final11-9th'!D38,'Final11-9th'!D39,'Final11-9th'!D40,'Final11-9th'!D41,'Final11-9th'!D42,'Final11-9th'!D43,'Final11-9th'!D44,'Final11-9th'!D45,'Final11-9th'!D46,'Final11-9th'!D47,'Final11-9th'!D48,'Final11-9th'!D49,'Final11-9th'!D50)
the purpose of this function is to get the math’s marks from the sheet “final11-9<SUP>th</SUP>” of every student by writing only the serial no in individual sheet.
is there any other simple way which is the solution to my problem or this formulae can be simplified.
thanks<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Robert Mika]

Hi abdul, and welcome to the board.
You may use VLOOKUP or INDEX/MATCH but if you post an example of your data it woudl be better to understand what you are trying to achive.

 

[Yahya]

أhi abdul
as report said try Index with match, like this

=INDEX('Final11-9th'!$D$8:$D$83,Sheet2!$G$9)

 

[abdul43]

Hi abdul, and welcome to the board.
You may use VLOOKUP or INDEX/MATCH but if you post an example of your data it woudl be better to understand what you are trying to achive.

i am new to this forum please will you help me by telling that how can i submit my excel file i have tried to find the upload or submit link but could not get sucess.

 

[abdul43]

Excelent solution Thank you very much.

أhi abdul
as report said try Index with match, like this

=INDEX('Final11-9th'!$D$8:$D$83,Sheet2!$G$9)

 

[Yahya]

i am new to this forum please will you help me by telling that how can i submit my excel file i have tried to find the upload or submit link but could not get sucess.

you can't upload file here, but you can use this tools it will help:
Excel jeanie OR MrExcel HTML Maker OR Borders-Copy-Paste

MrExcel HTML Maker

Excelent solution Thank you very much.

you are welcome