Date Lookup

Hi, assuming the following data:
A B
1 31-Dec-2009 50
2 4-Jan-2010 30
3 5-Jan-2010 20

In C, I have the date for the first day of every month e.g. 1-Jan-2010. In
the adjacent cell in D, i need to lookup the value in B for the first number
that occur for that month (in this case 4-Jan and so, the number should be
30). I tried various formula but as there are no 1-Jan in column A, it
returns the value of 50 (31-Dec). Anyone here has a solution for this? Thanks

Hi,

If there was a 1 jan in your data what would you want to return then, an
exact match or the next highest?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ck13" wrote:

Hi, assuming the following data:
A B
1 31-Dec-2009 50
2 4-Jan-2010 30
3 5-Jan-2010 20

In C, I have the date for the first day of every month e.g. 1-Jan-2010. In
the adjacent cell in D, i need to lookup the value in B for the first number
that occur for that month (in this case 4-Jan and so, the number should be
30). I tried various formula but as there are no 1-Jan in column A, it
returns the value of 50 (31-Dec). Anyone here has a solution for this? Thanks

Hi Mike,

If there is an exact match, i would like it to return the exact match.

"Mike H" wrote:

Hi,

If there was a 1 jan in your data what would you want to return then, an
exact match or the next highest?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ck13" wrote:

Hi, assuming the following data:
A B
1 31-Dec-2009 50
2 4-Jan-2010 30
3 5-Jan-2010 20

In C, I have the date for the first day of every month e.g. 1-Jan-2010. In
the adjacent cell in D, i need to lookup the value in B for the first number
that occur for that month (in this case 4-Jan and so, the number should be
30). I tried various formula but as there are no 1-Jan in column A, it
returns the value of 50 (31-Dec). Anyone here has a solution for this? Thanks

Hi

Try the following Array entered formula
=INDEX(A1:B3,MATCH(TRUE,TEXT(C1,"mmm")=TEXT(A1:A3, "mmm"),0),2)

To Array enter or amend, use Control+Shift+Enter (CSE) not just Enter.
When you use CSE, Excel will insert curly braces { } around the
formula. Do not type the braces yourself.

{=INDEX(A1:B3,MATCH(TRUE,TEXT(C1,"mmm")=TEXT(A1:A3 ,"mmm"),0),2)}
--
Regards
Roger Govier

ck13 wrote:
Hi, assuming the following data:
A B
1 31-Dec-2009 50
2 4-Jan-2010 30
3 5-Jan-2010 20

In C, I have the date for the first day of every month e.g. 1-Jan-2010. In
the adjacent cell in D, i need to lookup the value in B for the first number
that occur for that month (in this case 4-Jan and so, the number should be
30). I tried various formula but as there are no 1-Jan in column A, it
returns the value of 50 (31-Dec). Anyone here has a solution for this? Thanks

Hi,

In that case there are 2 options.

1. If column A is sorted ascending then you can get away with

=INDEX(B1:B10,COUNTIF(A1:A10,""&C1)+1)

2. If column A isn't sorted you need

=INDEX(B1:B10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10," "&C1)+1),A1:A10,0))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ck13" wrote:

Hi Mike,

If there is an exact match, i would like it to return the exact match.

"Mike H" wrote:

Hi,

If there was a 1 jan in your data what would you want to return then, an
exact match or the next highest?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ck13" wrote:

Hi, assuming the following data:
A B
1 31-Dec-2009 50
2 4-Jan-2010 30
3 5-Jan-2010 20

In C, I have the date for the first day of every month e.g. 1-Jan-2010. In
the adjacent cell in D, i need to lookup the value in B for the first number
that occur for that month (in this case 4-Jan and so, the number should be
30). I tried various formula but as there are no 1-Jan in column A, it
returns the value of 50 (31-Dec). Anyone here has a solution for this? Thanks

Hi
I forgot to add, I placed the date to be found (01 Jan 2010) in cell C1
--
Regards
Roger Govier

Roger Govier wrote:
Hi

Try the following Array entered formula
=INDEX(A1:B3,MATCH(TRUE,TEXT(C1,"mmm")=TEXT(A1:A3, "mmm"),0),2)

To Array enter or amend, use Control+Shift+Enter (CSE) not just Enter.
When you use CSE, Excel will insert curly braces { } around the
formula. Do not type the braces yourself.

{=INDEX(A1:B3,MATCH(TRUE,TEXT(C1,"mmm")=TEXT(A1:A3 ,"mmm"),0),2)}
--
Regards
Roger Govier

ck13 wrote:
Hi, assuming the following data:
A B
1 31-Dec-2009 50
2 4-Jan-2010 30 3 5-Jan-2010 20

In C, I have the date for the first day of every month e.g.
1-Jan-2010. In the adjacent cell in D, i need to lookup the value in B
for the first number that occur for that month (in this case 4-Jan and
so, the number should be 30). I tried various formula but as there are
no 1-Jan in column A, it returns the value of 50 (31-Dec). Anyone here
has a solution for this? Thanks

Or, slightly shorter, with 01 Jan 2010 in C1

{=INDEX(A1:B3,MATCH(TRUE,MONTH(C1)=MONTH(A1:A3),0) ,2)}
--
Regards
Roger Govier

Roger Govier wrote:
Hi

Try the following Array entered formula
=INDEX(A1:B3,MATCH(TRUE,TEXT(C1,"mmm")=TEXT(A1:A3, "mmm"),0),2)

To Array enter or amend, use Control+Shift+Enter (CSE) not just Enter.
When you use CSE, Excel will insert curly braces { } around the
formula. Do not type the braces yourself.

{=INDEX(A1:B3,MATCH(TRUE,TEXT(C1,"mmm")=TEXT(A1:A3 ,"mmm"),0),2)}
--
Regards
Roger Govier

ck13 wrote:
Hi, assuming the following data:
A B
1 31-Dec-2009 50
2 4-Jan-2010 30 3 5-Jan-2010 20

In C, I have the date for the first day of every month e.g.
1-Jan-2010. In the adjacent cell in D, i need to lookup the value in B
for the first number that occur for that month (in this case 4-Jan and
so, the number should be 30). I tried various formula but as there are
no 1-Jan in column A, it returns the value of 50 (31-Dec). Anyone here
has a solution for this? Thanks

Hi Mike,

Thanks you once again.. it works

"Mike H" wrote:

Hi,

In that case there are 2 options.

1. If column A is sorted ascending then you can get away with

=INDEX(B1:B10,COUNTIF(A1:A10,""&C1)+1)

2. If column A isn't sorted you need

=INDEX(B1:B10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10," "&C1)+1),A1:A10,0))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ck13" wrote:

Hi Mike,

If there is an exact match, i would like it to return the exact match.

"Mike H" wrote:

Hi,

If there was a 1 jan in your data what would you want to return then, an
exact match or the next highest?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ck13" wrote:

Hi, assuming the following data:
A B
1 31-Dec-2009 50
2 4-Jan-2010 30
3 5-Jan-2010 20

In C, I have the date for the first day of every month e.g. 1-Jan-2010. In
the adjacent cell in D, i need to lookup the value in B for the first number
that occur for that month (in this case 4-Jan and so, the number should be
30). I tried various formula but as there are no 1-Jan in column A, it
returns the value of 50 (31-Dec). Anyone here has a solution for this? Thanks

Glad I could help and thanks for the feedback
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ck13" wrote:

Hi Mike,

Thanks you once again.. it works

"Mike H" wrote:

Hi,

In that case there are 2 options.

1. If column A is sorted ascending then you can get away with

=INDEX(B1:B10,COUNTIF(A1:A10,""&C1)+1)

2. If column A isn't sorted you need

=INDEX(B1:B10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10," "&C1)+1),A1:A10,0))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ck13" wrote:

Hi Mike,

If there is an exact match, i would like it to return the exact match.

"Mike H" wrote:

Hi,

If there was a 1 jan in your data what would you want to return then, an
exact match or the next highest?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ck13" wrote:

Hi, assuming the following data:
A B
1 31-Dec-2009 50
2 4-Jan-2010 30
3 5-Jan-2010 20

In C, I have the date for the first day of every month e.g. 1-Jan-2010. In
the adjacent cell in D, i need to lookup the value in B for the first number
that occur for that month (in this case 4-Jan and so, the number should be
30). I tried various formula but as there are no 1-Jan in column A, it
returns the value of 50 (31-Dec). Anyone here has a solution for this? Thanks