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.

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.

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.

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.

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.