look up more than one cell eg grid
My column headings all show a students results (from 1 to 100) except for
column A which shows a students age, just written as a year and month, and by cross referencing down and across I can see the grade, which I would then like to input in a cell against the students name on worksheet 2: 0 1 2 3 4 5 6 7 8 etc 5/ 1 E E E E E D D D D 5/2 E E E E E E E D 5/3 E E E E E E E E etc If anyone can tell me how to do this, once I have set up the grid I would be so grateful. |
look up more than one cell eg grid
Hi,
Here's one way... If the range of grades is Sheet1!B2:CX37 (I don't know how many ages you have going down column A) if the student's age is in Sheet2!A1 and his/her score in Sheet2!B1 then you could type the following formula into the cell on Sheet2 where you would like to have that student's grade... =INDEX(Sheet1!$B$2:$CX$37,MATCH(A1,Sheet1!$A$2:$A$ 37,FALSE),MATCH(B1,Sheet1!$B$1:$CX$1,FALSE)) Ken Johnson |
look up more than one cell eg grid
Very many thanks
"Ken Johnson" wrote: Hi, Here's one way... If the range of grades is Sheet1!B2:CX37 (I don't know how many ages you have going down column A) if the student's age is in Sheet2!A1 and his/her score in Sheet2!B1 then you could type the following formula into the cell on Sheet2 where you would like to have that student's grade... =INDEX(Sheet1!$B$2:$CX$37,MATCH(A1,Sheet1!$A$2:$A$ 37,FALSE),MATCH(B1,Sheet1!$B$1:$CX$1,FALSE)) Ken Johnson |
look up more than one cell eg grid
"Ken Johnson" wrote: Hi, Here's one way... If the range of grades is Sheet1!B2:CX37 (I don't know how many ages you have going down column A) if the student's age is in Sheet2!A1 and his/her score in Sheet2!B1 then you could type the following formula into the cell on Sheet2 where you would like to have that student's grade... =INDEX(Sheet1!$B$2:$CX$37,MATCH(A1,Sheet1!$A$2:$A$ 37,FALSE),MATCH(B1,Sheet1!$B$1:$CX$1,FALSE)) Ken Johnson |
look up more than one cell eg grid
You're welcome.
Always happy to help a fellow teacher. Ken Johnson |
All times are GMT +1. The time now is 04:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com