Vlookup a specific time and return name only if specific time is between a range of Current Time

[Lacan]

Hi Guys,

Here is the challenge, have this table:


09:00 Jen
10:00 Frank
11:00 Tom
12:00 Hannah


The goal is to Vlookup time in column A and return name in column B only if the time in column A is between range of +20 minutes of current time and -20 minutes of current time.


Can you help me?


Thanks to all.

 

[lrobbo314]

Try this,

=INDEX(B2:B5,SUMPRODUCT(((A2:A5>=NOW()-(20/24/60))*(A2:A5<=NOW()+(20/24/60)))*ROW(INDIRECT("1:" & COUNTA(A2:A5)))))

 

[DanteAmor]

If you have schedules spaced by one hour, then in a certain period you will not find a name, for example, if the current time is between 8:21 and 8:39, +/- 20 minutes, you will not find any time, since the results would be from 8:01 to 8:41 and 8:29 to 8:59, then you do not find any time.
But if you have schedules every 20 minutes, then the following formula is enough:

Excel Workbook
	A	B	C
1	Time	NAME	Name Current Time
2	16:00	Name1	Name7
3	16:20	Name2
4	16:40	Name3
5	17:00	Name4
6	17:20	Name5
7	17:40	Name6
8	18:00	Name7
9	18:20	Name8
10	18:40	Name9
11	19:00	Name10
12	19:20	Name11
13	19:40	Name12
14	20:00	Name13
15	20:20	Name14
16	20:40	Name15
Sheet

 

[Lacan]

Try this,

=INDEX(B2:B5,SUMPRODUCT(((A2:A5>=NOW()-(20/24/60))*(A2:A5<=NOW()+(20/24/60)))*ROW(INDIRECT("1:" & COUNTA(A2:A5)))))

First, thanks for your support.

Tried the formula however only returns the first name even though the hour still changes.

Can you please check?

Thanks again.

 

[Lacan]

If you have schedules spaced by one hour, then in a certain period you will not find a name, for example, if the current time is between 8:21 and 8:39, +/- 20 minutes, you will not find any time, since the results would be from 8:01 to 8:41 and 8:29 to 8:59, then you do not find any time.
But if you have schedules every 20 minutes, then the following formula is enough:

Sheet

	A	B	C
1	Time	NAME	Name Current Time
2	16:00	Name1	Name7
3	16:20	Name2
4	16:40	Name3
5	17:00	Name4
6	17:20	Name5
7	17:40	Name6
8	18:00	Name7
9	18:20	Name8
10	18:40	Name9
11	19:00	Name10
12	19:20	Name11
13	19:40	Name12
14	20:00	Name13
15	20:20	Name14
16	20:40	Name15

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 80px;"><col style="width: 80px;"><col style="width: 129px;"></colgroup><tbody>
</tbody>

Formulas

Cell Formula C2 =VLOOKUP(TIMEVALUE(TEXT(NOW(),"HH:MM")),A2:B16,2,1) <tbody> </tbody>

<tbody>
</tbody>

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

First, thanks for your support.

The Formula works great however I would like to mantain the initial hour structure (like 09:00; 10:00; 11:00...).

Just ask with that do you have any ideas?

Thanks a lot.

 

[DanteAmor]

First, thanks for your support.
The Formula works great however I would like to mantain the initial hour structure (like 09:00; 10:00; 11:00...).
Just ask with that do you have any ideas?
Thanks a lot.

Try this:

Excel Workbook
	A	B	C	D
1	Time	NAME	Name Current Time	Current Time
2	09:00	Name1	Name3	10:43
3	10:00	Name2
4	11:00	Name3
5	12:00	Name4
6	13:00	Name5
7	14:00	Name6
8	15:00	Name7
9	16:00	Name8
10	17:00	Name9
11	18:00	Name10
12	19:00	Name11
13	20:00	Name12
14	21:00	Name13
15	22:00	Name14
16	23:00	Name15
Sheet

 

[Lacan]

Works Great!!!!!
Excellent!!!
Thank you very much!!!!!

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.

 

[Lacan]

Try this:

Excel Workbook
	A	B	C	D
1	Time	NAME	Name Current Time	Current Time
2	09:00	Name1	Name3	10:43
3	10:00	Name2
4	11:00	Name3
5	12:00	Name4
6	13:00	Name5
7	14:00	Name6
8	15:00	Name7
9	16:00	Name8
10	17:00	Name9
11	18:00	Name10
12	19:00	Name11
13	20:00	Name12
14	21:00	Name13
15	22:00	Name14
16	23:00	Name15
Sheet

@DanteAmor

Dear DanteAmor,

Hope you are fine.
As you were the mentor of this wonderful solution just reach you because would like the same result however has to change the format hour to 09H, 10H, 11H... instead of 09:00, 10:00, 11:00... as you can check in table below.
Can you please help?
Thanks again my friend.

	Time	NAME	Name Current Time	Current Time
	09H	Name1	=IFERROR(OFFSET(B1,SUMPRODUCT((A2:A5>=TIMEVALUE(TEXT(NOW()-"00:20","hh:mm")))*(A2:A5<=TIMEVALUE(TEXT(NOW()+"00:20","hh:mm")))*ROW(A2:A5))-1,0),"without coincidences")	10:43
	10H	Name2
	11H	Name3
	12H	Name4
	13H	Name5
	14H	Name6
	15H	Name7
	16H	Name8
	17H	Name9
	18H	Name10
	19H	Name11
	20H	Name12
	21H	Name13
	22H	Name14
	23H	Name15

 

[DanteAmor]

If your data is like this:

Libro1
	A	B	C	D
1	Time	NAME	Name Current Time	Current Time
2	09H	Name1	11H	11:35:50 a. m.
3	10H	Name2
4	11H	Name3
5	12H	Name4
6	13H	Name5
7	14H	Name6
8	15H	Name7
9	16H	Name8
10	17H	Name9
11	18H	Name10
12	19H	Name11
13	20H	Name12
14	21H	Name13
15	22H	Name14
16	23H	Name15
Hoja1

Try this:

=LET(A2:A16,a,IFERROR(INDEX(a,SUMPRODUCT((SUBSTITUTE(a,"H","")+0>=TEXT(NOW()-"00:20","hh")+0)*(SUBSTITUTE(a,"H","")+0<=TEXT(NOW()+"00:20","hh")+0)*ROW(a))-ROW($A$2)+1),"without coincidences"))