Formula Suggestion required

[Guest]

I have a table with the following information

Pay Date End Period
07-09-2006 0809
05-10-2006 0909
02-11-2006 1009
30-11-2006 1109
21-12-2006 1209

What I want to be able to do is enter a date for example 15/11/2006, and it
tell me what the end period would be. Therefore it would look for the first
higher paydate, in this case 30/11/2006 so therefore return a value of 1109.

Many Thanks in advance for your assistance

 

[Don Guillett]

If you have the toolpak installed
=EOMONTH(D12,0)
if not
=DATE(YEAR(D13),MONTH(D13)+1,0)

 

[David Biddulph]

If your pay date table is at A2:A6, and your end period codes at B2:B6, with
your input date at A9, try
=INDEX(B2:B6,1+MATCH(A9,A2:A6,1))

 

[Guest]

David

Thanks this works great unless you enter a date that is in the table, then
it returns the following period, I have resolved this by adding an If iserror
vlookup

=IF(ISERROR(VLOOKUP(A9,A2:B6,2,FALSE)),INDEX(B2:B6,1+MATCH(A9,A2:A6,1)),VLOOKUP(A9,A2:B6,2,FALSE)).

However if I enter a date before the first date in the table i receive an N/A?

PLease can you let m e know how this could be resolved?