SOLVED

Showing a cell value if between a time range

New Contributor

Good day

 

I am looking for help with a formula to show a cell value from a range of times.

 

I have a list of specific times, as per below.

 

Mozil1989_2-1669283485388.png

 

I have also added 8 time ranges (3 hours over 24 hour period) I have named these ranges with specific names like Morning, Noon, Afternoon, Evening, etc.

 

Mozil1989_3-1669283551591.png

 

I need to display the period names for these ranges next to the list of specific times, if it falls in between the start and finish times. 

 

I have tried xlookup, meridian and if formulas, as well as trying to combine them but I do not seem to get the expected result.

 

Attached is the worksheet, the first tab is the time ranges and names, the second tab is the specific times I need names for. (Morning, Noon, Afternoon, Evening)

 

Please help! 

2 Replies
best response confirmed by Mozil1989 (New Contributor)
Solution

@Mozil1989 

I'd organize the lookup table slightly differently:

S2008.png

You can then use VLOOKUP or XLOOKUP:

S2009.png

The formula in B2 is

=VLOOKUP(A2,$E$2:$F$10,2)

or

=XLOOKUP(A2,$E$2:$E$10,$F$2:$F$10,"",-1)

Hi @Hans Vogelaar

This has worked perfectly, thank you for your feedback.