Simple Lookup?

[tonyotter]

There is probably a simple solution to this one but my brain is failing me!

in sheet 1 i have cells for users to enter any time from 0600 upto 1800
eg in A1 12:20 B1 15:04 C1 15:28
in A2 14:00 B2 15:12 C2 16:00

in sheet 2 I have numbers starting at 06:00 (in A1) down to 18:00 (in A721)
I am trying to get a formula within cells in column B which will return a 1 in the cells (B) next to the time in A if the time data in column A isqual to the time in Sheet 1 A1, including all times upto and including the time in A2.

So for example: where Sheet B1 = 15:04 and B2 = 15:12
Sheet 2 A544 is 15:03 so B544 is blank
A545 is 15:04 so B545 = 1
B546 through to B553 will show 1, then B554 will return blank.

I hope ths makes sense!!

I have tried a few methods for lookups and match, but to no avail. Any help is greatly recieved.

 

[T. Valko]

There is probably a simple solution to this one but my brain is failing me!

in sheet 1 i have cells for users to enter any time from 0600 upto 1800
eg in A1 12:20 B1 15:04 C1 15:28
in A2 14:00 B2 15:12 C2 16:00

in sheet 2 I have numbers starting at 06:00 (in A1) down to 18:00 (in A721)
I am trying to get a formula within cells in column B which will return a 1 in the cells (B) next to the time in A if the time data in column A isqual to the time in Sheet 1 A1, including all times upto and including the time in A2.

So for example: where Sheet B1 = 15:04 and B2 = 15:12
Sheet 2 A544 is 15:03 so B544 is blank
A545 is 15:04 so B545 = 1
B546 through to B553 will show 1, then B554 will return blank.

I hope ths makes sense!!

I have tried a few methods for lookups and match, but to no avail. Any help is greatly recieved.

Let's see if I got this straight...


Sheet1:

• A1 = 6:05 AM
• A2 = 6:08 AM

Is this what you want on sheet2:

Book1
	A	B
2	6:00 AM
3	6:01 AM
4	6:02 AM
5	6:03 AM
6	6:04 AM
7	6:05 AM	1
8	6:06 AM	1
9	6:07 AM	1
10	6:08 AM	1
11	6:09 AM
12	6:10 AM
13	6:11 AM
14	6:12 AM
15	6:13 AM
Sheet1

How did you fill the times in Sheet2?

Chances are when you try a "lookup" formula the times won't match due to "rounding errors".

 

[tonyotter]

Thats exactly what I'm trying to do. But i need the formulas in Sheet2 B to apply to the data sets A1(start time),A2(finish time) and further sets in columns B, C, D, an E.
There will never be an overlap in finsh time and following start time.

 

[T. Valko]

Thats exactly what I'm trying to do. But i need the formulas in Sheet2 B to apply to the data sets A1(start time),A2(finish time) and further sets in columns B, C, D, an E.
There will never be an overlap in finsh time and following start time.

Ugh!

5 sets of time periods!

Here's an example of how to do it with 2 time periods. You'll be able to figure it out for the other 3!

Time periods on Sheet1:

Book1
	A	B
1	6:05 AM	6:10 AM
2	6:08 AM	6:11 AM
Sheet1

Results on Sheet2:

Book1
	A	B
2	6:00 AM
3	6:01 AM
4	6:02 AM
5	6:03 AM
6	6:04 AM
7	6:05 AM	1
8	6:06 AM	1
9	6:07 AM	1
10	6:08 AM	1
11	6:09 AM
12	6:10 AM	1
13	6:11 AM	1
14	6:12 AM
15	6:13 AM
Sheet2

Enter this formula on Sheet2 in cell B2:

=IF(AND(Sheet1!A$1<=A2,A2<=Sheet1!A$2),1,"")&IF(AND(Sheet1!B$1<=A2,A2<=Sheet1!B$2),1,"")

Copy down as needed.

You didn't answer my question about:

How did you fill the times in Sheet2?

Chances are when you try a "lookup" formula the times won't match due to "rounding errors".

Try it and see if it works out.

 

[tonyotter]

The times in sheet2 were entered as general numbers seperated by a colon.
Anyway... the formula works a treat. exactly what I was striving for.

Many Thanks for your help.

Tony

 

[T. Valko]

The times in sheet2 were entered as general numbers seperated by a colon.
Anyway... the formula works a treat. exactly what I was striving for.

Many Thanks for your help.

Tony

Good deal! Thanks for the feedback!