• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Display result if value is between 2 times [INDEX + MATCH]

Flick

New Member
Hi all,

Getting used to using INDEX and MATCH as a multiple criteria VLOOKUP, but have come to a dead end on this one approach.

We have a meeting room at work where people need to write in a book to "claim" the room for that time, but no one uses the book. So I came up with an idea for a spreadsheet that they can just check whenever they want to see if the room is booked.

On the left in column A, I have times from 09:00 to 17:30, showing every half hour between those times. There's also a date at the top of column A. Column B results will change depending on the date - this data comes from the table on the right (G2:J7). Adam has booked a meeting from 09:00 to 09:59, and in column A at 09:00, it shows his name, but not at 09:30. I'm really not sure on how to display his name in that gap where an error shows at 09:30.

Does anyone have an idea on how to do that? All help appreciated.
 

Attachments

  • Meeting - Chandoo.xlsx
    11.9 KB · Views: 0
Hi,
May be this with CSE:
=IFERROR(INDEX($G$2:$K$7,MATCH(1,($G$2:$G$7=$A$1)*($H$2:$H$7<=A2)*($I$2:$I$7>=A2)*($K$2:$K$7=FALSE),0),4),"...")

Regards,
 
Khalid, that's wonderful, and thanks for introducing me to IFERROR, it will come in handy. There's one slight problem I'm having though, and that's if a meeting starts at any of these times (14:30, 16:00, 17:30), it doesn't display a name - not sure if that has anything to do with your formula or not?
 
Khalid, that's wonderful, and thanks for introducing me to IFERROR, it will come in handy. There's one slight problem I'm having though, and that's if a meeting starts at any of these times (14:30, 16:00, 17:30), it doesn't display a name - not sure if that has anything to do with your formula or not?

I am not sure, I just enter the time stamps without overlaps and getting results.
Can you see the file and check the result with variations?
 

Attachments

  • Meeting - Chandoo-2.xlsx
    13.4 KB · Views: 0
I am not sure, I just enter the time stamps without overlaps and getting results.
Can you see the file and check the result with variations?

There's no overlaps, but have a look at this screenprint:
HTFKV4B.png


It doesn't display Deepak's name at 16:00, and it will do the same for 14:30 and 17:30. I don't think it has anything to do with your formula as it works fine for the other times, but these specific times appear to be problematic, wondered if it was something you have come across before?
 

Attachments

  • Meeting - Chandoo-3.xlsx
    13.7 KB · Views: 0
Interesting...
I've never encountered this before, and don't know what exactly is going wrong.
I tried some experiments with:
16:29 instead of 16:30
17:29 instead of 17:30

Don't know what is the difference with just 1 minute.
May be someone can give some lights and came with different idea.

Regards,
 
I may have found out why.

Evaluating the formula, it seems Excel differs in how it calculates these times. 16:00 can show as both 0.666666666666666 and 0.666666666666667, which do not match. However, the formula is trying to see if 0.666666666666667 is less than or equal to 0.666666666666666, which it is not.

wDmQzI1.png
 
I may have found out why.

Evaluating the formula, it seems Excel differs in how it calculates these times. 16:00 can show as both 0.666666666666666 and 0.666666666666667, which do not match. However, the formula is trying to see if 0.666666666666667 is less than or equal to 0.666666666666666, which it is not.

wDmQzI1.png

Hi,
Thanks for the evaluation...

If this is the matter, can we wrap our range with =round(range,2) and round(criteria,2)?
something like:
=IFERROR(INDEX($G$2:$K$7,MATCH(1,($G$2:$G$7=$A$1)*(ROUND($H$2:$H$7,2)<=ROUND(A2,2))*(ROUND($I$2:$I$7,2)>=ROUND(A2,2))*($K$2:$K$7=FALSE),0),4),"...")
with ctrl+shift+enter

Regards,
 
Back
Top