To calculate a response time based on an assigned status like "urgent" or "critical", you can build a simple table with status details, then use VLOOKUP to lookup look up and calculate the the "target time" - the time by which a response is due.
The reference to F5:G7 is absolute so that the formula can be copied down the table. Column index is provided as 2 to retrieve response times, and matching is set to "exact" by using zero for the last argument.
The response returned by VLOOKUP is then added directly to the datetime value in column B.
Because response times in column G are entered in standard Excel time format (2:00, 8:00, etc.) these time values can be added directly without any conversion, and Excel displays the calculated target time in column D.
VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.