OFFSET Function with dynamic refereence

[JohnZ1156]

I have the following formula which works when referencing from cell A327:

=OFFSET(A327,13,4,1,1)

Column A contains dates. rather than referencing cell A327, I would like the OFFSET Function to dynamically change to the first day of the current month.
Something like this.
=OFFSET(DATE(YEAR(TODAY(),MONTH(TODAY(),1),13,4,1,1)

Happy Valentine's Day !!!

 

[arthurbr]

Perhaps

=index($a$1:$a$1000,match(eomonth(today(),-1)+1,$a$1:$a$1000,0))

 

[Fluff]

Another option

=INDEX(E2:E1000,MATCH(EOMONTH(TODAY(),-1)+1,A2:A1000,0)+13)

 

[JohnZ1156]

Another option

=INDEX(E2:E1000,MATCH(EOMONTH(TODAY(),-1)+1,A2:A1000,0)+13)

Hi Fluff, Nice to hear from you again.

Sorry, but I made a mistake in my original decription.
First day of each current year. By month was a mistake.

=OFFSET(DATE(YEAR(TODAY(),1,1),13,4,1,1)

 

[Fluff]

In that case just replace the eomonth in my suggestion with the date function

 

[JohnZ1156]

In that case just replace the eomonth in my suggestion with the date function

Do you mean like this?

=INDEX(E2:E1000,MATCH(DATE(TODAY(),-1)+1,A2:A1000,0)+13)

I guess I don't fully understand, because this did not work

 

[JohnZ1156]

Do you mean like this?

=INDEX(E2:E1000,MATCH(DATE(TODAY(),-1)+1,A2:A1000,0)+13)

I guess I don't fully understand, because this did not work

Nevermind, I tried a couple of things, and I think I got it to work.
Again, thank you.

 

[Fluff]

What I meant was like

=INDEX(E2:E1000,MATCH(DATE(YEAR(TODAY()),1,1),A2:A1000,0)+13)

 

[JohnZ1156]

What I meant was like

=INDEX(E2:E1000,MATCH(DATE(YEAR(TODAY()),1,1),A2:A1000,0)+13)

Ha, That's exactly what I ended up with and it worked!
Your suggestion definitely pointed me in the right direction.
With one exception, I base most of my dates from one cell reference.

so actually, here is my final formula from your suggestion:
=INDEX(E2:E1000,MATCH(DATE(YEAR(Bank 1!B15),1,1),A2:A1000,0)+13)

And worked into the big picture, here is the whole enchilada:
=IF(AND(DAY(Bank 1!$B$15)>=DAY(1),DAY(Bank 1!$B$15)<=DAY(4)),VLOOKUP(DATE(YEAR(Bank 1!$B$15),MONTH(Bank 1!$B$15)-1,1),'Bank 2'!A:E,5,FALSE),"YTD = "&TEXT(INDEX('Bank 2'!E2:E1000,MATCH(DATE(YEAR(Bank 1!B15),1,1),'Bank 2'!A2:A1000,0)+13),"#,##0.00"))

Cell: Bank 1!B15 simply contains =today()
But, this way I can temporarily change that value to another date, and all of my results on all other sheets will show resutls for and around that date.

Again, I can't think you enough. I was struggling with this all day yesterday.
For some reason, actually, due to my inexperience with Excel, I spent most of the day trying to figure out how to do this by using the OFFSET function.
Turns out, after all, I didn't even need that function. There's always another way.
Thank you

 

[Fluff]

Glad to help & thanks for the feedback.