How can I lookup a cell that's to the left?

[Weasel]

I've got a sheet that looks like this:


Code:
--------------------

Week Day Sales
3/4 10
3/5 20
3/6 30
Week 1 3/7 20
3/8 10
3/9 20
3/10 10
Total 120
3/11
3/12
3/13
Week 2 3/14
3/15
3/16
3/17
Total

--------------------


The cells with the week's listed (i.e. Week 1, Week 2, etc...) are
merged for the week. So for example if the dates for 3/4 through 3/10
were entered into cells B3 through B9 then cells A3 through A9 are
merged together with Week 1 in it.

What I'm trying to do is use a LOOKUP function (or any other function
that would work) to display the week in a cell. In A1 I have entered a
date. Based on that date I want cell A2 to list what week it is. So for
example if I put 3/7/06 into cell A1 I'd want A2 to display "Week 1".

As a sidenote is it possible to also change it so A2 displays it as
"week 1". By that I mean changing the W in Week to lowercase. Thanks in
advance for any help.

 

[Biff]

Hi!

Try this:

=LOWER(LOOKUP(REPT("Z",255),A3:INDEX(A3:A20,MATCH(A1,B3:B20,0))))

Adjust the range references to suit.

Merged cells are a disease! Cure the disease!

Biff

 

[Paul Lautman]

I've got a sheet that looks like this:


Code:
--------------------

Week Day Sales
3/4 10
3/5 20
3/6 30
Week 1 3/7 20
3/8 10
3/9 20
3/10 10
Total 120
3/11
3/12
3/13
Week 2 3/14
3/15
3/16
3/17
Total

--------------------


The cells with the week's listed (i.e. Week 1, Week 2, etc...) are
merged for the week. So for example if the dates for 3/4 through 3/10
were entered into cells B3 through B9 then cells A3 through A9 are
merged together with Week 1 in it.

What I'm trying to do is use a LOOKUP function (or any other function
that would work) to display the week in a cell. In A1 I have entered a
date. Based on that date I want cell A2 to list what week it is. So
for example if I put 3/7/06 into cell A1 I'd want A2 to display "Week
1".

As a sidenote is it possible to also change it so A2 displays it as
"week 1". By that I mean changing the W in Week to lowercase. Thanks
in advance for any help.

Use the =OFFSET function

 

[Biff]

Use the =OFFSET function

Care to show us how you would use offset to solve this?

Biff