return financial period if value between 2 dates

[Guest]

I have the following table set up on sheet2
Periods Start End
1 1-Apr-06 20-Apr-06
2 21-Apr-06 18-May-06
3 19-May-06 15-Jun-06
etc.

On sheet 1, when the date is entered in Column A, I want a function in
Column B to show what financial period that date would be in.

i.e.
A B
05-10-2006 2

 

[Guest]

One way ..

Sheet2's reference table as posted is assumed within A1:C4

In Sheet1,

Assume dates running in A2 down

Put in B2, array-enter (press CTRL+SHIFT+ENTER):
=INDEX(Sheet2!$A$2:$A$4,MATCH(1,(A2>=Sheet2!$B$2:$B$4)*(A2<=Sheet2!$C$2:$C$4),0))
Copy B2 down. Adapt the Sheet2 ranges to suit the extent of your actuals
before copy down.

 

[Guest]

Try this:

=INDEX(Sheet2!A2:A4,MATCH(Sheet1!A1,Sheet2!B2:B4,1))

 

[Guest]

I'm getting a value of #NA for all

One way ..

Sheet2's reference table as posted is assumed within A1:C4

In Sheet1,

Assume dates running in A2 down

Put in B2, array-enter (press CTRL+SHIFT+ENTER):
=INDEX(Sheet2!$A$2:$A$4,MATCH(1,(A2>=Sheet2!$B$2:$B$4)*(A2<=Sheet2!$C$2:$C$4),0))
Copy B2 down. Adapt the Sheet2 ranges to suit the extent of your actuals
before copy down.

 

[Guest]

I'm getting a value of #NA for all

That's probably because you didn't "array-enter" the formula (press
CTRL+SHIFT+ENTER), as advised in my response. Go back and click inside the
formula bar for B2, then press CTRL+SHIFT+ENTER (instead of just pressing
ENTER) to confirm the formula. If you did it correctly, you'd see that Excel
wraps curly braces { } around the formula (see in the formula bar), and the
formula will work properly. With it properly entered in B2, copy down.

---

 

[Guest]

Thought I had... but it worked this time.

Thanks very much

 

[Max]

Welcome. Glad you got it working.