Lookup a date and roundup to the next date from a specified list

[eagow]

Hi

I'm trying to create a payroll spreadsheet and I have the following in the rows:

1 Date (increasing by day) e.g. 16Jul, 17Jul, 18Jul, 19Jul, 20Jul etc.
2 Payroll Cutoff date

In the second row I would like a formula that looks up the cutoff date using the date above. I.e. it will need to find the date from a list which is the next cutoff date (greater than or equal date).

In a separate worksheet I then have the list of payrol cutoff dates in a named array called 'cutoff':

20Jul
10Aug
20Aug
etc.

I have tried using the MATCH and INDEX formulas, so in cell A2 I tried:
=INDEX(cutoff,MATCH(EK2,cutoff,-1))
but I just get a #N/A error.

Any ideas how to get around this?

 

[barry houdini]

Hello eagow, welcome to MrExcel

You need MATCH with a "match type" of 1 if the cutoff dates are in ascending order. Try

=INDEX(cutoff,MATCH(EK2-1,cutoff,1)+1)

You might get #N/A error if EK2-1 is before the first date in cutoff list. You can work around that by adding an earlier date (or zero) to the start of cutoff.

Alternatively you can use an "array formula"

=MIN(IF(cutoff>=EK2,cutoff))

confirmed with CTRL+SHIFT+ENTER

Note: assumes your data consists of "real" dates (not text)

 

[eagow]

Wow, wasn't expecting a reply so soon! Thanks Barry

That works great- I guess I had the dates sorting order mixed up in my head! I'll have a play with the array formula as well- just starting using them. It's opened up a whole new world of excel I never knew about!