vlookup question

[Guest]

A B
C D
1 =vlookup(b$1,C$1$7,2,0) January-06 January-06
1
2
January-06 6
3
January-06 7
4
January-06 8
5
January-06 9
6
January-06 14
7
January-06 15

Asume vlookup formula is copied down to 17 and col C is the same down to row
17 and col D goes down to row 17 with the date continuing to ascend from D1
to D17.

The result in A1 is 1 and A2 is also 1, in fact all the way down to A17 it
is 1. I want the formula to recognize all of the January-06's in
Col C and return 1,6,7,8,9,14,15,.....all the way to the last workday in
january ascending from A1 to A17. HOW can this be done?

Thank you,
BD

 

[Bryan Hessey]

Not quite sure what you are tying to arrive at, but, your formula uses
B$1 so every item will be the same, the first item.

If you are trying to constitute a date based on the nth occurance
(specified in column B) from a month/year as specified in column C and
a day specified in column D then in A1 and formula drag down:

=DATE(YEAR(OFFSET(C$1,B1-1,0)),
MONTH(OFFSET(C$1,B1-1,0)),OFFSET(D$1,B1-1,0))

should do that as:

17/01/2006 1 Jan-06 17
18/01/2006 2 Jan-06 18
20/01/2006 3 Jan-06 20
31/01/2006 4 Jan-06 31
2/02/2006 5 Feb-06 2
6/02/2006 6 Feb-06 6
13/02/2006 7 Feb-06 13

If you require someting different please reply.

--