Two dimensional look up
 

HI,
I am looking for a function which returns a value by looking in to 2
dimensional table.For example
Joe Mike Sandy Andy
Maths 10 35 10 35
Science 20 65 20 65
Trignomtry 30 98 30 98
Eenglish 40 65 40 65

If I pass Joe and Maths as two inputs to fuction,it should give me 10.
I was using round about methods using IF conditions and LOOKUP functions.
Butif the number of of columns and rows are huge,its not a practical solution.

Can you please suggest me a practical solution which I cam implement for
looking in to huge tables containing like 100 rows and 100 columns.

THanks in advance.

Two dimensional look up
 

Hi,

I think the way you have started the is flawed because what will happen if 2
people have the same name. Having said that based upon your sample data try
this which assumes you table is in A1 (actually blank) to E5

=SUMPRODUCT((A2:A5=F1)*(B1:E1=G1)*(B2:E5))

Where F1 = Subject
G1 = Name

Mike
"2 D lookup" wrote:

HI,
I am looking for a function which returns a value by looking in to 2
dimensional table.For example
Joe Mike Sandy Andy
Maths 10 35 10 35
Science 20 65 20 65
Trignomtry 30 98 30 98
Eenglish 40 65 40 65

If I pass Joe and Maths as two inputs to fuction,it should give me 10.
I was using round about methods using IF conditions and LOOKUP functions.
Butif the number of of columns and rows are huge,its not a practical solution.

Can you please suggest me a practical solution which I cam implement for
looking in to huge tables containing like 100 rows and 100 columns.

THanks in advance.

Two dimensional look up
 

One way...

Assume you're table is in the range A1:E5

Lookup values:

A10 = maths
B10 = Joe

=VLOOKUP(A10,A1:E5,MATCH(B10,A1:E1,0),0)

--
Biff
Microsoft Excel MVP


"2 D lookup" <2 D wrote in message
...
HI,
I am looking for a function which returns a value by looking in to 2
dimensional table.For example
Joe Mike Sandy Andy
Maths 10 35 10 35
Science 20 65 20 65
Trignomtry 30 98 30 98
Eenglish 40 65 40 65

If I pass Joe and Maths as two inputs to fuction,it should give me 10.
I was using round about methods using IF conditions and LOOKUP functions.
Butif the number of of columns and rows are huge,its not a practical
solution.

Can you please suggest me a practical solution which I cam implement for
looking in to huge tables containing like 100 rows and 100 columns.

THanks in advance.

Two dimensional look up
 

Hey Thanks Mike.
Ya I know,Two guys having same name would create problems.I just gave it for
an example.
Your solution is working fine.
Thanks a lot.



"Mike H" wrote:

Hi,

I think the way you have started the is flawed because what will happen if 2
people have the same name. Having said that based upon your sample data try
this which assumes you table is in A1 (actually blank) to E5

=SUMPRODUCT((A2:A5=F1)*(B1:E1=G1)*(B2:E5))

Where F1 = Subject
G1 = Name

Mike
"2 D lookup" wrote:

HI,
I am looking for a function which returns a value by looking in to 2
dimensional table.For example
Joe Mike Sandy Andy
Maths 10 35 10 35
Science 20 65 20 65
Trignomtry 30 98 30 98
Eenglish 40 65 40 65

If I pass Joe and Maths as two inputs to fuction,it should give me 10.
I was using round about methods using IF conditions and LOOKUP functions.
Butif the number of of columns and rows are huge,its not a practical solution.

Can you please suggest me a practical solution which I cam implement for
looking in to huge tables containing like 100 rows and 100 columns.

THanks in advance.

Two dimensional look up
 

Glad I could help

"2 D lookup" wrote:

Hey Thanks Mike.
Ya I know,Two guys having same name would create problems.I just gave it for
an example.
Your solution is working fine.
Thanks a lot.



"Mike H" wrote:

Hi,

I think the way you have started the is flawed because what will happen if 2
people have the same name. Having said that based upon your sample data try
this which assumes you table is in A1 (actually blank) to E5

=SUMPRODUCT((A2:A5=F1)*(B1:E1=G1)*(B2:E5))

Where F1 = Subject
G1 = Name

Mike
"2 D lookup" wrote:

HI,
I am looking for a function which returns a value by looking in to 2
dimensional table.For example
Joe Mike Sandy Andy
Maths 10 35 10 35
Science 20 65 20 65
Trignomtry 30 98 30 98
Eenglish 40 65 40 65

If I pass Joe and Maths as two inputs to fuction,it should give me 10.
I was using round about methods using IF conditions and LOOKUP functions.
Butif the number of of columns and rows are huge,its not a practical solution.

Can you please suggest me a practical solution which I cam implement for
looking in to huge tables containing like 100 rows and 100 columns.

THanks in advance.