I was wondering if there's a built in worksheet function that would allow the
lookup of row and column data.

For example I have a table like this, that I want to fill in.

James Kirk
Hockey [cell B2]
Soccer

I have a data like this that I want to find the answer for:
[Other.Sheet]
Name Sport Score
James Soccer 10
Kirk Hockey 5
James Hockey 10
Kirk Soccer 10

So in cell B2, I want to write a formula that looks in [Other.Sheet].
If "Name" matches, and "Sport" matches, then return "Score". Can this be
done in Excel? Thanks in advance.

Debra Dagleish explains how to:
http://www.contextures.com/xlFunctions03.html (for =index(match()))

Susan wrote:

I was wondering if there's a built in worksheet function that would allow the
lookup of row and column data.

For example I have a table like this, that I want to fill in.

James Kirk
Hockey [cell B2]
Soccer

I have a data like this that I want to find the answer for:
[Other.Sheet]
Name Sport Score
James Soccer 10
Kirk Hockey 5
James Hockey 10
Kirk Soccer 10

So in cell B2, I want to write a formula that looks in [Other.Sheet].
If "Name" matches, and "Sport" matches, then return "Score". Can this be
done in Excel? Thanks in advance.

--

Dave Peterson

in B2:

=SUMPRODUCT(--(Sheet2!$A$2:$A$5=B$1),--(Sheet2!$B$2:$B$5=$A2),Sheet2!$C$2:$C$5)

Sheet2 is the sheet with your data (Other.sheet)

Copy across and down in your table

HTH

"Susan" wrote:

I was wondering if there's a built in worksheet function that would allow the
lookup of row and column data.

For example I have a table like this, that I want to fill in.

James Kirk
Hockey [cell B2]
Soccer

I have a data like this that I want to find the answer for:
[Other.Sheet]
Name Sport Score
James Soccer 10
Kirk Hockey 5
James Hockey 10
Kirk Soccer 10

So in cell B2, I want to write a formula that looks in [Other.Sheet].
If "Name" matches, and "Sport" matches, then return "Score". Can this be
done in Excel? Thanks in advance.

Assume your data in Sheet 1
"Sport", "Name" and "Score" are define name ranges

In sheet 2
A2: holds Hockey
A3: holds Soccer
B1: holds James
C1: holds Kirk

B2: =SUMPRODUCT(--(Sport=Sheet2!$A2),--(Name=Sheet2!B$1),Score)
copy accross and down

"Susan" wrote:

I was wondering if there's a built in worksheet function that would allow the
lookup of row and column data.

For example I have a table like this, that I want to fill in.

James Kirk
Hockey [cell B2]
Soccer

I have a data like this that I want to find the answer for:
[Other.Sheet]
Name Sport Score
James Soccer 10
Kirk Hockey 5
James Hockey 10
Kirk Soccer 10

So in cell B2, I want to write a formula that looks in [Other.Sheet].
If "Name" matches, and "Sport" matches, then return "Score". Can this be
done in Excel? Thanks in advance.