VLOOKUP With Multiple Criteria
 

Hi All.

I need to create a formula to evalute the value returned by the VLOOKUP. When the VLOOKUP makes a match if needs to check if the value of the returned value is:
Criteria 1
Between 06:00:00 and 06:30:00

Criteria 2
08:00:00 and 08:30:00

Criteria 3
09:00:00

Should the evaluated value fall between one of the specified value it needs to return a value of "Yes". If however the value falls outside the three mentioned criteria it needs to return a value of "No".

-----------------------------------------------------------------

Dear SalientAnimal, Good Afternoon.

I did an example for you.
Here it´s : DEC-13-2010-EXCELBANTER_VLOOKUP_With_Multiple_Criteria_HOURS.x ls
Take a look at this and tell me if it worked for you.

Thanks Mazzaropi this worked :-). I really appreciate the assistance. I now need to add an additional field to the formula. Where the agent is entitled to a 1 hour/30 min break based on their shift. i.e 6hour shift gets 30min break, both other shifts get 1hour break. should they exceed their break by more than 3minutes then the result should be a no, regardless if their shift times are within the predefined criteria.

Regards,
SalientAnimal

-------------------------------------------------------------------------
Dear SalienteAnimal, Good Morning.

I couldn't understanding well your explanation.
Could you modify the original worksheet I sent you, puting one example that what you want now in a clear way?
I believe that this will easier the understanding and solution process to your necessity.
You can use the same procedure that I used to save and share the worksheet.(www.4shared.com).

I waiting your answer.

Good Morning Mazzaropi,

Unfortunately I am unable to access the link that you provided. Could I possibly e-mail the file? Alternatively I will try and explain it again using the criteria fields:

Criteria 1
Between 06:00:00 and 06:30:00
Agent has a break of 00:33:00

Criteria 2
08:00:00 and 08:30:00
Agent has a break of 01:03:00

Criteria 3
09:00:00
Agent has a break of 01:03:00

Should the evaluated value fall between one of the specified value it needs to return a value of "Yes". If however the value falls outside the three mentioned criteria it needs to return a value of "No". Once the first evaluation is complete it then needs to check the break times. If the agent exceeds the allocated break time all other criteria should fail and it should be marked as a "No".

Thanks Again for your assistance, I really do appreciate it.

Hello Again Mazzaropi,

I finally go the link to work, for some reason it had a bracket and / in the link that wasn't meant to be there. I have uploaded the file to http://www.4shared.com/file/d8u4Ee8a...port_2011.html.

Here's a description of the file:
DATA_SHEET - Data extracted to evaluate

- Column 15 - Staff Time Adjusted (Q)
Used to evaluate the shift times, i.e Between 06:00:00 and 06:30:00, Between 08:00:00 and 08:30:00, and 09:00:00




- Column 16 Break Time Adjusted (R)
Used to evaluate the break times, i.e 00:33:00 and 01:03:00


Monthly_Adherence - Where the initial formula you supplied is being used.


Weekly_Adherence - Weekly Consolidation of the Monthly sheet.


Do you need any additional information from me?

Thanks,

Any advice on this query yet?