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

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

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

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

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

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