Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup help
In Excel I need to do a lookup function that derives its results from the
intersection of a row and Column for a grade equivalent. It is a combined math test score that takes the Score from the math computation section as it is displayed horizontally across the top of the table and then checks against the score from the applied mathematics section that is displayed vertically down a column. The intersection of that row and column give a grade equivalent. How can I make the lookup check both horizontal and vertical sections and return a score? Any information will be appreciated Thanks tom |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup help
Assumptions:
Math computation score in A2, applied math in A3, table in B1:Z100 (with labels in row 1, column b) =INDEX(B2:Z100,MATCH(A3,B2:B100),MATCH(A3,C1:Z1)) Index allows exactly what you asked, an intersection of row number with column number. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tom" wrote: In Excel I need to do a lookup function that derives its results from the intersection of a row and Column for a grade equivalent. It is a combined math test score that takes the Score from the math computation section as it is displayed horizontally across the top of the table and then checks against the score from the applied mathematics section that is displayed vertically down a column. The intersection of that row and column give a grade equivalent. How can I make the lookup check both horizontal and vertical sections and return a score? Any information will be appreciated Thanks tom |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup help
Oops, wrong array area.
Change formula to =INDEX(C2:Z100,MATCH(A3,B2:B100),MATCH(A3,C1:Z1)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Assumptions: Math computation score in A2, applied math in A3, table in B1:Z100 (with labels in row 1, column b) =INDEX(B2:Z100,MATCH(A3,B2:B100),MATCH(A3,C1:Z1)) Index allows exactly what you asked, an intersection of row number with column number. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tom" wrote: In Excel I need to do a lookup function that derives its results from the intersection of a row and Column for a grade equivalent. It is a combined math test score that takes the Score from the math computation section as it is displayed horizontally across the top of the table and then checks against the score from the applied mathematics section that is displayed vertically down a column. The intersection of that row and column give a grade equivalent. How can I make the lookup check both horizontal and vertical sections and return a score? Any information will be appreciated Thanks tom |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup help
Tom
Let's say your data including the header row and column are in A1 to G10 and your 2 lookup values are in H1(Row header) & H2(Column header). Try this array formula to return the intersect of those 2 values =INDEX(A1:G10,MATCH(H2,A1:A10,0),MATCH(H1,A1:G1,0) ) This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Tom" wrote: In Excel I need to do a lookup function that derives its results from the intersection of a row and Column for a grade equivalent. It is a combined math test score that takes the Score from the math computation section as it is displayed horizontally across the top of the table and then checks against the score from the applied mathematics section that is displayed vertically down a column. The intersection of that row and column give a grade equivalent. How can I make the lookup check both horizontal and vertical sections and return a score? Any information will be appreciated Thanks tom |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup help
Luke
I have a sheet called Grades and on that sheet in cell c4 i have a score stored for the math computation score stored on that same sheet in cell e7 I have the score for math applications i need the score in cell c4 to be looked up on a sheet called combined math in cells b2:aa2 and I need the score stored in e7 to be looked up in column a3:a25 on the combined math sheet. The intersection of those two numbers I need to be returned to cell i2 on the grades sheet. Does this make sense? I appreciate any help you can give me Tom "Luke M" wrote: Oops, wrong array area. Change formula to =INDEX(C2:Z100,MATCH(A3,B2:B100),MATCH(A3,C1:Z1)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Assumptions: Math computation score in A2, applied math in A3, table in B1:Z100 (with labels in row 1, column b) =INDEX(B2:Z100,MATCH(A3,B2:B100),MATCH(A3,C1:Z1)) Index allows exactly what you asked, an intersection of row number with column number. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tom" wrote: In Excel I need to do a lookup function that derives its results from the intersection of a row and Column for a grade equivalent. It is a combined math test score that takes the Score from the math computation section as it is displayed horizontally across the top of the table and then checks against the score from the applied mathematics section that is displayed vertically down a column. The intersection of that row and column give a grade equivalent. How can I make the lookup check both horizontal and vertical sections and return a score? Any information will be appreciated Thanks tom |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup help
Hi Mikw
I have a sheet called Grades and on that sheet in cell c4 i have a score stored for the math computation score stored on that same sheet in cell e7 I have the score for math applications i need the score in cell c4 to be looked up on a sheet called combined math in cells b2:aa2 and I need the score stored in e7 to be looked up in column a3:a25 on the combined math sheet. The intersection of those two numbers I need to be returned to cell i2 on the grades sheet. Does this make sense? I appreciate any help you can give me Tom "Mike H" wrote: Tom Let's say your data including the header row and column are in A1 to G10 and your 2 lookup values are in H1(Row header) & H2(Column header). Try this array formula to return the intersect of those 2 values =INDEX(A1:G10,MATCH(H2,A1:A10,0),MATCH(H1,A1:G1,0) ) This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Tom" wrote: In Excel I need to do a lookup function that derives its results from the intersection of a row and Column for a grade equivalent. It is a combined math test score that takes the Score from the math computation section as it is displayed horizontally across the top of the table and then checks against the score from the applied mathematics section that is displayed vertically down a column. The intersection of that row and column give a grade equivalent. How can I make the lookup check both horizontal and vertical sections and return a score? Any information will be appreciated Thanks tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |