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.

 

[Lacan]

Dear @DanteAmor

First thank you very much for your fast reply.
Im not sure want Im doing incorrect because it is not working with me.
As initially in C2 want to return the exact name in column B2:B16 which match between now time -00:30 and +00:30.
Can you please give a check?
Thank you very much.

Livros Brasil.xlsx
	A	B	C	D	E
1	Time	NAME	Name Current Time	Current Time
2	09H	Name1	without coincidences	20:24:08
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
17
18
Folha2

 

[Peter_SSs]

Give this a try.
BTW, when using XL2BB with formulas, best to tick the 'Cell formulas' box so we can easily see the formulas under the worksheet.

24 02 19.xlsm
	A	B	C	D
1	Time	NAME	Name Current Time	Current Time
2	09H	Name1	Name2	19/02/2024 11:19
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
Lookup time

Cell Formulas
Range	Formula
C2	C2	=LET(strt,HOUR(MOD(NOW()-"00:30",1)),h,LEFT(A2:A16,2)+0,INDEX(FILTER(B2:B16,(h>=strt)*(h<=strt+1),"without coincidences"),1))
D2	D2	=NOW()

 

[Lacan]

Give this a try.
BTW, when using XL2BB with formulas, best to tick the 'Cell formulas' box so we can easily see the formulas under the worksheet.

View attachment 107052

24 02 19.xlsm
	A	B	C	D
1	Time	NAME	Name Current Time	Current Time
2	09H	Name1	Name2	19/02/2024 11:19
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
Lookup time

Cell Formulas
Range	Formula
C2	C2	=LET(strt,HOUR(MOD(NOW()-"00:30",1)),h,LEFT(A2:A16,2)+0,INDEX(FILTER(B2:B16,(h>=strt)*(h<=strt+1),"without coincidences"),1))
D2	D2	=NOW()

Dear @Peter_SSs

Nice to hear from you Im glad you are ok.
Sure will record the note of XL2BB.
Formula above works perfect will give some tests tomorrow.
If need some adjustments will let you know.
Thank you very much!!!

 

[Peter_SSs]

You're welcome. Hope it continues to work for you. Thanks for the follow-up.