Nov 24 2022 02:09 AM
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.
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.
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!
Nov 24 2022 05:19 AM
SolutionI'd organize the lookup table slightly differently:
You can then use VLOOKUP or XLOOKUP:
The formula in B2 is
=VLOOKUP(A2,$E$2:$F$10,2)
or
=XLOOKUP(A2,$E$2:$E$10,$F$2:$F$10,"",-1)
Nov 24 2022 05:57 AM
Nov 24 2022 05:19 AM
SolutionI'd organize the lookup table slightly differently:
You can then use VLOOKUP or XLOOKUP:
The formula in B2 is
=VLOOKUP(A2,$E$2:$F$10,2)
or
=XLOOKUP(A2,$E$2:$E$10,$F$2:$F$10,"",-1)