Vlookup date from multiple occurences

**markmac234** · 05-24-2008, 05:25 AM

Basically, I would like to auto populate columns C:D on the 'Recent Detachments' worksheet. This worksheet has all staff members in alphabetical order, so I can see their most relevant trips away.

Many thanks,

Mac

**Ron Coderre** · 05-24-2008, 10:20 AM

Well, I should have been clearer...

It seems that every person listed has dates that are within the past two months through the next four months. Which dates do you want to see for which people? Also, if you fill in ALL of the results, that will help.

**markmac234** · 05-24-2008, 11:57 AM

I have now entered detached dates from 2006-2009.

Kind regards,

Mac

**Ron Coderre** · 05-24-2008, 04:50 PM

Hmmm....not the additional detail I needed.

Using the latest file you posted....
Today is 24-May-2008
2 Mths ago is 24-Mar-2008
4 Mths from now is 24-Sep-2008

Harrison has 4 entries:

Staff 	Name	Depart		Return
456	Harrison	28-Oct-06		09-Dec-06
456	Harrison	05-May-07		16-Jun-07
456	Harrison	06-Apr-08		22-May-08
456	Harrison	27-Apr-08		12-Jun-08
456	Harrison	25-Aug-08		30-Oct-08

Do you want the entries that:
Start AND end within the date range?
Start within the date range?
End within the date range?
Something else?

If there are more than one that meet the criteria,
do you want the one that:
Ends latest?
Begins latest?
Something else?

**markmac234** · 05-25-2008, 05:23 AM

Ron

I would like the criteria to be the start date falling in the 6 month window. If that person does not have a date that matches this window, then nothing will be displayed. Basically, I need to keep a track of those members of staff who have recently departed (i.e. 2 months ago), are still away and those who will be departing in the next 4 months; this is the 6 month window that I need to track for each person.

Regards,

Mac

**Gearcutter** · 05-25-2008, 02:19 PM

Hi, I hope I have got the right end of the stick! in the attached file I've assumed that 2 months is 60 days and that 4 months is 120 days both can be changed to suit whatever period that you want.
Hope this helps. Regards Howard

**markmac234** · 05-25-2008, 03:07 PM

Howard,

Many thanks for the updated workbook, and whilst your formula does return the values I am after, the 17 names in the 'Recent detachments' worksheet must remain static. I have slightly altered the attachment to explain this.

Regards,

Mac