Excel Lookup Question

[ritpg]

I've got the following Excel sheet in which the work figures represent
the weekly and cumulative hours in a person's hours budget:

05-Jan-09 12-Jan-09 19-Jan-09
etc.
Joe Smith
Work 40 40 35
Cumulative Work 40 80 115
Mary Jones
Work 25 40 35
Cumulative Work 25 65 100
John Jefferson
Work 35 40 35
Cumulative Work 35 75 110
etc.

And I have a 2nd sheet as follows:

Current Date: 12-Jan-09

Cumulative Hours
Budgeted to Date
Mary Jones 65
Joe Smith 80
John Jefferson 75
etc.

Note that the names on the 2nd sheet are not in the same order as the
1st sheet. I want to be able to change the Current Date on the 2nd
sheet and have the Cumulative Hours Budgeted to Date column populated
automatically.

I know there are folks out there much smarter about Excel than I. Any
and all inputs will be greatly appreciated.

Thanks,
Terry

 

[ritpg]

I've got the following Excel sheet in which the work figures represent
the weekly and cumulative hours in a person's hours budget:

                               05-Jan-09   12-Jan-09   19-Jan-09
etc.
Joe Smith
   Work                        40           40              35
   Cumulative Work       40            80              115
Mary Jones
   Work                        25           40              35
   Cumulative Work       25            65              100
John Jefferson
   Work                        35           40              35
   Cumulative Work       35            75               110
etc.

And I have a 2nd sheet as follows:

Current Date:   12-Jan-09

                           Cumulative Hours
                           Budgeted to Date
Mary Jones                    65
Joe Smith                      80
John Jefferson                75
etc.

Note that the names on the 2nd sheet are not in the same order as the
1st sheet.  I want to be able to change the Current Date on the 2nd
sheet and have the Cumulative Hours Budgeted to Date column populated
automatically.

I know there are folks out there much smarter about Excel than I.  Any
and all inputs will be greatly appreciated.

Thanks,
Terry

I suspect the answer has something to do with a combination of the
VLOOKUP and HLOOKUP functions. Or perhaps the INDEX function would do
it. Any thoughts?

Thanks.

 

[ritpg]

I suspect the answer has something to do with a combination of the
VLOOKUP and HLOOKUP functions.  Or perhaps the INDEX function would do
it.  Any thoughts?

Thanks.- Hide quoted text -

- Show quoted text -

My! It's quiet out there.

I've come to the conlusion 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:

Sheet 1 with 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
cell 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, 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

 

[ritpg]

My!  It's quiet out there.

I've come to the conlusion 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:

Sheet 1 with 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
cell 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, 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- Hide quoted text -

- Show quoted text -

Duh!!!! I just fixed the problem. For some unknown reason, I was
ignoring the 3rd argument of the MATCH function. It must contain a
-1, 0 or +1. Leaving the 3rd argument blank defaults it to +1 which
is not what I wanted. When I entered a value of 0 (=MATCH(A3,'Sheet
2'!$A$2:$A$13,0)+3) (which requires an exact match and allows the
lookup array to be in any order) and bumped the row increment from 2
to 3, everything worked fine. Life is good!

 

[ritpg77]

Duh!!!!  I just fixed the problem.  For some unknown reason, I was
ignoring the 3rd argument of the MATCH function.  It must contain a
-1, 0 or +1.  Leaving the 3rd argument blank defaults it to +1 which
is not what I wanted.  When I entered a value of 0 (=MATCH(A3,'Sheet
2'!$A$2:$A$13,0)+3) (which requires an exact match and allows the
lookup array to be in any order) and bumped the row increment from 2
to 3, everything worked fine.  Life is good!- Hide quoted text -

- Show quoted text -

Yoo hoo!!! Anyone out there???? I used to post to this NG and get
lots of responses. Has everyone moved from usenet to the web?
Someone, please respond.