Vlookup date from multiple occurences

[markmac234]

Hi

I am having trouble doing a VLOOKUP, whereby I have a column of staff that are frequently detached from the workplace (See extract sample). One member of staff could be away 4 times in a year and I need to place the most relevant detached date (which would be 2 months ago and 4 months from today) into another table. This other table would then show all members of staff in alphabetical order with just one detached and return date. If the date falls out of the above parameters (6 month window), nothing would be displayed.

Many thanks,

Mac

[Ron Coderre]

The file you attached is a good starting point for us....
but, it would be helpful if you also showed us what values
you're looking for in the result table.

[markmac234]

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]

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]

I have now entered detached dates from 2006-2009.

Kind regards,

Mac

[Ron Coderre]

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?