Source
r/excel•
Introvert

Lookup date equal to or less than given date with multiple criteria

unsolved

I've been struggling with this problem for a while now. Googling around has gotten me close but not quite there yet.

​

I have two sheets, 'Leave Detail' and 'Unpaid Leave'.

​

A unique identifier for each employee is in column A on each sheet. Each report has a start date and an end date.

Problems: The leave dates in the 'Leave Detail' sheet can span multiple days. They can also be only part days - Having a combined date and time in the Start and End columns. There can also be multiple entries (typically no more than two, and more than one is not terrible common) for the same date.

I need to lookup the 'Leave Type' in column E and 'Comment' in column H from 'Leave Detail' and return it against the corresponding leave in the 'Unpaid Leave sheet'.

​

Scenario:

​

Team Member A takes planned leave for 01/01/2019 to 02/01/2019.

Team Member A is unpaid for 3.5 hours on 02/01/2019 as they had insufficient leave balance available.

I need the unpaid leave record in 'Unpaid Leave' to return the leave type and comment from 'Leave Detail'.

I've tried a mix of approximate lookup with sorted sheets and arrayed 'MAX' formulas to no avail...

I did have the sheet working with just returning matches based on the same start date and employee ID, but it requires manual matching of leave that spans more than one day.

​

Suggestions welcome!

​

Example sheet attached below:

https://docs.google.com/spreadsheets/d/1b_RCNUjdb922h6J03pMev4HcYlIDk0fk1CBffQ53dQo/edit?usp=sharing

​

Edit: updated to correct link

Posted Jan 24, 2019, 1:27 PM UTC · edited

13
ePaint1•

This is the exact same problem I just solved 2 days ago. I used VBA. I simply stopped trying to use regular formulas when I reached 100 characters of formula. It got unreadable.

Let me know if VBA is in the scope and I'll share the code.

1
IntrovertOP•

For those interested I have solved this problem.

​

The trick was to first match the date, THEN the employee ID in the IF statement. I think I did it the other way around previously.

Final formula is below:

=INDEX(RANGE TO RETURN,MATCH(DATE TO LOOKUP,IF(EMOPLOYEE ID RANGE = EMOPLOYEE ID TO LOOK UP,DATE RANGE TO SEARCH),1))

Some of the date values in my lookup range were Date and time in one so I was looking up against a helper column that converted the serial number a whole number. (date/time field - MOD(date/time field,1))

​

Solution Verified

1
AutoModeratorMOD•

Hello!

It looks like you tried to award a ClippyPoint, but you need to reply to a particular user's comment to do so, rather than making a new top-level comment.

Please reply directly to any helpful users and Clippy, our bot will take it from there. If your intention was not to award a ClippyPoint and simply mark the post as solved, then you may do that by clicking Set Flair. Thank you!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1