vlookup problem

when cell C1 is changed to between midnight and 7am an error is generated in cells C2 and C12

That is correct.

The earliest time in the table is 07:01, so anything before that will create an error. To prevent it you will need to add an earlier time frame to the table.

To suppress the error you could use an IFERROR such as;

Formula:

Please Login or Register  to view this content.

Or;

Formula:

Please Login or Register  to view this content.

Thank you guys. This was my first time on any forum. No matter what I tried I couldn't get it to work. Now with your help it works like magic.

Repeating Ford's comments in post #4.

If you update your profile so that we know which version of excel you are working with then we can most likely provide you with better answers.

Ford mentioned that you don't need the WEEKDAY(B3) function as B3 only contains a single digit, but B3 contains the formula, =WEEKDAY(C1). With this in mind, you dont need B3 at all, you can just skip straignt to WEEKDAY($C$1) in your lookup formula.

If you read the help file and look at the optional parameters of WEEKDAY, you will see that you can change it to move the start of the week as needed, by changing that so that the week starts on Monday instead of Sunday, you don't need to check the weekday twice, only if it is before Saturday.

Also, if you try to avoid repeating sections of formula when you don't need to, it can be easier to edit and troubleshoot when needed. As the only part of the formula that changes is the lookup range based on the weekday, you only need to define that range based on the result of the logical test, not the entire vlookup formula.

Combining all of the points raised by Ford and myself, you could shorten your whole formula to

=VLOOKUP(MOD($C$1,1),IF(WEEKDAY($C$1,2)<6,H1:I5,H7:I8),2)

and still achieve the same result.

Of course, this still doesn't take into consideration that times before 07:01 will return an error. Your first post contains a statement, "when cell C1 is changed to between midnight and 7am an error is generated in cells C2 and C12"not a question, you have told us what happens but not given us any hint as to what you want to happen in that time frame, which is why the replies you recieved contain short and vague answers.

In short.

Correct Office version in your profile = More suitable suggestions.
Better information in your question = better suggestions.

I could make the list longer, but I think you get the idea