The named range is for convenience and readability only.
you might also use theabsolute reference$G$5:$H$7.
However, note how this tangles up the formula with the category data.
If the days per category changes, the formula will need to be edited.
If more categories are added, moreIF functionswill need to be added.
This is not an ideal way to solve the problem.
Finally, we use zero (0) for therange_lookupargumentto force an exact match.
So far, so good.
With XLOOKUP
For this problem, VLOOKUP works fine.
WORKDAY uses the start date anddaysto create a date in the future, automatically ignoring Saturday and Sunday.
WORKDAY can also ignore holidays as well, if they are provided as a range with valid dates.
See theWORKDAYpage for more information.
With hours
If you are usingdatetimes(i.e.
date + time), you might adjust thecategoriestable to showExcel hours(i.e.
8:00, 12:00, etc.)
and use the same formula to determine a due date measured in hours.
This works, because hours in Excel arefractional parts of 1 day.
You will also need to change thenumber formatused in column E to show date and time.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
