I have a need to do what I believe is called a two dimensional
search. I want to populate a cell with the contents of a cell that
exists at the intersection of a row and column each of which contains
a known value.

I've come to the conclusion that a combination of the HLOOKUP and
MATCH functions should do it. However, I don't seem to be able to get
it to work.

Here are the real Excel sheets and matching cell equations so you have
all the info. I am trying to populate column B of Sheet 1 with the
values located in Sheet 2 at the intersection whose row contains the
person's name and whose column contains the date in cell B1 of Sheet
1.

Sheet 1 with incorrect results from equations (shown below) in columns
B and C:

A B C
1 Date: 11-Apr-09
2 Name: Cum Hrs From Row No.
3 Joe 0 11
4 Sue #REF! 14
5 Mike 70 12
6 Mary 0 11

Sheet 2:

1 4-Apr-09 11-Apr-09 18-Apr-09 25-Apr-09
2 Mary
3 Period Hrs. 80 80 70 60
4 Cum Hrs 80 160 230 290
5 Joe
6 Period Hrs. 75 75 60 80
7 Cum Hrs 75 150 210 290
8 Sue
9 Period Hrs. 60 60 70 80
10 Cum Hrs 60 120 190 270
11 Mike
12 Period Hrs. 70 70 60
80
13 Cum Hrs 70 140 200 280

The following is the equation I have come up with so far for Sheet 1
cells B3:B6

=HLOOKUP($B$1,'Sheet 2'!$A$1:$E$13,MATCH(A3,'Sheet 2'!$A$2:$A
$13)+2).

I added 2 rows at the end to get the 2nd row following the row
containing the person's name.

I also broke out the equation

=MATCH(A3,'Sheet 2'!$A$2:$A$13)+2

and put it in cells C3:C6 to see what row the MATCH function is
returning.

As you can see, the MATCH function is not returning a correct value
and I have no idea why. When I
substitute the correct row number for the MATCH function in the
HLOOKUP function, I get the correct results. So I know the HLOOKUP
function is working correctly.

Anybody have any thoughts? Please?

Thanks.
Terry

On Mon, 15 Dec 2008 12:05:25 -0800 (PST), ritpg
wrote:

I have a need to do what I believe is called a two dimensional
search. I want to populate a cell with the contents of a cell that
exists at the intersection of a row and column each of which contains
a known value.

I've come to the conclusion that a combination of the HLOOKUP and
MATCH functions should do it. However, I don't seem to be able to get
it to work.

Here are the real Excel sheets and matching cell equations so you have
all the info. I am trying to populate column B of Sheet 1 with the
values located in Sheet 2 at the intersection whose row contains the
person's name and whose column contains the date in cell B1 of Sheet
1.

Sheet 1 with incorrect results from equations (shown below) in columns
B and C:

A B C
1 Date: 11-Apr-09
2 Name: Cum Hrs From Row No.
3 Joe 0 11
4 Sue #REF! 14
5 Mike 70 12
6 Mary 0 11

Sheet 2:

1 4-Apr-09 11-Apr-09 18-Apr-09 25-Apr-09
2 Mary
3 Period Hrs. 80 80 70 60
4 Cum Hrs 80 160 230 290
5 Joe
6 Period Hrs. 75 75 60 80
7 Cum Hrs 75 150 210 290
8 Sue
9 Period Hrs. 60 60 70 80
10 Cum Hrs 60 120 190 270
11 Mike
12 Period Hrs. 70 70 60
80
13 Cum Hrs 70 140 200 280

The following is the equation I have come up with so far for Sheet 1
cells B3:B6

=HLOOKUP($B$1,'Sheet 2'!$A$1:$E$13,MATCH(A3,'Sheet 2'!$A$2:$A
$13)+2).

I added 2 rows at the end to get the 2nd row following the row
containing the person's name.

I also broke out the equation

=MATCH(A3,'Sheet 2'!$A$2:$A$13)+2

and put it in cells C3:C6 to see what row the MATCH function is
returning.

As you can see, the MATCH function is not returning a correct value
and I have no idea why. When I
substitute the correct row number for the MATCH function in the
HLOOKUP function, I get the correct results. So I know the HLOOKUP
function is working correctly.

Anybody have any thoughts? Please?

Thanks.
Terry

Have a look a the MATCH function in help.
You will require a third parameter , Match_type, to the MATCH
function.
Set this parameter to 0 (zero) as your data in the $A$2:$A$13 range is
not sorted.

Hope this helps / Lars-Åke