Lookup the first date of the month

[BigBeachBananas]

Hi,
I'm looking for a way to look up the first date of the month in a range as well as its corresponding value. Please see the sample below. Thanks

book1.xlsb
	A	B	C	D	E
1	Sample Data			OutCome
2	Date	Treasury Rates		Date	Treasury Rates
3	12/31/2004	2.75		12/1/2004	2.6
4	12/30/2004	2.76		11/1/2004	2.34
5	12/29/2004	2.77
6	12/28/2004	2.77
7	12/27/2004	2.78
8	12/8/2004	2.59
9	12/7/2004	2.6
10	12/6/2004	2.6
11	12/3/2004	2.58
12	12/2/2004	2.62
13	12/1/2004	2.6
14	11/30/2004	2.63
15	11/29/2004	2.66
16	11/12/2004	2.49
17	11/10/2004	2.47
18	11/9/2004	2.46
19	11/8/2004	2.47
20	11/5/2004	2.44
21	11/4/2004	2.34
22	11/3/2004	2.32
23	11/2/2004	2.33
24	11/1/2004	2.34
Sheet3

 

[Joe4]

This can be done pretty easily with the new FILTER function.
In your Outcome section, enter this formula in the first cell under DATE:

=FILTER(A3:B24,DAY(A3:A24)=1)

It should return exactly what you show.

 

[BigBeachBananas]

This can be done pretty easily with the new FILTER function.
In your Outcome section, enter this formula in the first cell under DATE:

=FILTER(A3:B24,DAY(A3:A24)=1)

It should return exactly what you show.

Hi Joe4,
Thank you for the reply. Sorry for the incomplete sample data, but the first day of the month isn't always 1. Sometimes it can be the 2nd or the 3rd of the month.

 

[Joe4]

Hi Joe4,
Thank you for the reply. Sorry for the incomplete sample data, but the first day of the month isn't always 1. Sometimes it can be the 2nd or the 3rd of the month.

Ah, the dangers of oversimplified data examples (the reply answers the question you posed correctly, but does not work on your actual data because of the oversimplification)!

A few questions:
1. Is your data always sorted in date order, like you show in your example (sorted in descending order)?
2. Do you have any issues with adding/using a "helper" column?

 

[BigBeachBananas]

Ah, the dangers of oversimplified data examples (the reply answers the question you posed correctly, but does not work on your actual data because of the oversimplification)!

A few questions:
1. Is your data always sorted in date order, like you show in your example (sorted in descending order)?
2. Do you have any issues with adding/using a "helper" column?

1) Yes, It's in descending order (December to January)
2) No, I don't have an issue with adding an other column

 

[Joe4]

Excellent.

Then place this formula in cell C3 and copy down for all rows:

=IF(TEXT(A3,"mmyy")<>TEXT(A4,"mmyy"),1,0)

Then we can update the FILTER formula to this:

=FILTER(A3:B28,C3:C28=1)

 

[BigBeachBananas]

Excellent.

Then place this formula in cell C3 and copy down for all rows:

=IF(TEXT(A3,"mmyy")<>TEXT(A4,"mmyy"),1,0)

Then we can update the FILTER formula to this:

=FILTER(A3:B28,C3:C28=1)

It worked. Awesome. Thanks!

 

[Joe4]

You are welcome.