Match Date, Choose Next Value

[john_mc]

Hi All,

I'd like to be able to type in a date and then have excel look down the list of payments dates in column A, find the date after the payment start date entered, and then bring back the date that is x no. of weeks after it (the value also entered by a user).

However, as the dates are fortnightly periods, I actually need it to divide the number of weeks entered in the cell by 2 to get the correct number of fortnights (or cells) to offset.

So, if the user types in start date of 12th June 2006, the next payment date after this is the 22 June (the first payment date on the sheet). We will pay them for 4 weeks, so skip two fortnights and bring back the 20th July 2006.
While this example works, later dates dont'.

I think what I've got should work, but can't figure out why it doesn't.

Any help anyone could offer would be great,

Cheers,

John Mc

[psumvp]

Im not certain I follow what you are trying to do... However your match formulas in cell d10 and d13 do not have the full data range as arguments. After you update the end range to A9989, the formulas appear to work again... Since you have a match type of 1, it was just returning the last value in the array.

[john_mc]

Hi Psumvp,

thanks for this - not sure why i didn't spot it before, but I appreciate the time you took to have a look at it and let me know what the problem was. Once the date range was changed, it works like a charm.

Thanks again for your help,

Cheers,
John Mc