Vlookup date from multiple occurences

**markmac234** · 05-23-2008, 05:21 PM

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** · 05-23-2008, 08:35 PM

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** · 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:

Please Login or Register  to view this content.

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

**Gearcutter** · 05-26-2008, 04:35 AM

Hi Mac, I had to take a liberty in inserting an extra column in the 'all detachment' sheet so I could enter the formula for the Vlookup to pick up the staff numbers,that come into your criteria of dates, this can be of course be hidden.
Regards Howard

**markmac234** · 05-26-2008, 05:09 AM

Howard,

You are an absolute star!! This is FINALLY what I have been looking for. I would never in a million years have thought of placing IF and AND staements to the leftmost Vlookup column. Many many thanks to you.

Kind regards,

Mac

**VBA Noob** · 05-26-2008, 06:20 AM

markmac234,

Please read forum rules below and then add the link to the other forum where you have cross posted this question

VBA Noob

**Gearcutter** · 05-26-2008, 09:12 AM

Hi Mac,
Many thanks for the feedback glad to be of help
Regards Howard

**markmac234** · 05-26-2008, 10:31 AM

http://www.excelforum.com/showthread.php?t=644665

This is a similar problem that I had, and although it was resolved, the solution on this page is perfect for my workbook. Thanks again to Howard.

Regards,

Mac

Vlookup date from multiple occurences

LinkBack

Thread Tools

Rate This Thread

Display