INTERVIEW PREP · FRESHER EDITION

Excel Formulas for

Service Delivery Ops
Master these formulas to ace your SDO interview

All Formulas Lookup Statistical Logical Text Date & Time

LO O KU P & R E F E R E N C E

VLOOKUP LOOKUP HLOOKUP LOOKUP

Searches for a value in the first column of a range and returns a Like VLOOKUP but searches horizontally across rows. Useful
value in the same row from another column. Most commonly when your data is laid out with headers in a row (e.g., months
used in SDO for matching ticket IDs, agent names, or SLA across columns).
categories.
=HLOOKUP(lookup_value, table_array, row_index_num,
=VLOOKUP(lookup_value, table_array, col_index_num,

=HLOOKUP("Apr", B1:M3, 2, 0)
=VLOOKUP(A2, TicketDB!A:D, 3, 0) Find "Apr" in row 1, return value from row 2 (e.g., ticket volume for
Find ticket A2 in sheet TicketDB, return column 3 (e.g., Status). April).
Use 0 for exact match.

INDEX + MATCH LOOKUP XLOOKUP LOOKUP

Powerful combo that overcomes VLOOKUP's left-column Modern replacement for VLOOKUP/HLOOKUP (Excel 2019+).
limitation. MATCH finds the position; INDEX returns the value. Can search left or right, returns a range, and handles errors
Preferred in modern SDO reporting. natively. Ideal for dynamic dashboards.

=INDEX(return_range, MATCH(lookup_val, lookup_range =XLOOKUP(lookup_value, lookup_array, return_array,

=INDEX(C2:C100, MATCH("TKT-045", A2:A100, 0)) =XLOOKUP(A2, AgentID, AgentName, "Not Found")

Find ticket TKT-045 anywhere in column A, return its resolution Look up agent ID and return name. Shows "Not Found" if missing
time from column C. instead of an error.

S T AT I S T I C A L & A G G R E G AT I O N

COUNTIF / COUNTIFS STATISTICAL SUMIF / SUMIFS STATISTICAL

Count cells that meet one (COUNTIF) or multiple (COUNTIFS) Sum values based on conditions. Useful for totaling resolution
criteria. Essential for tracking open tickets, SLA breaches, or times, costs, or effort hours by category, team, or priority.
agent-wise ticket counts.
=SUMIF(range, criteria, sum_range)
=COUNTIF(range, criteria) =SUMIFS(sum_range, range1, crit1, range2, crit2)
=COUNTIFS(range1, criteria1, range2, criteria2)

=SUMIFS(D2:D100, C2:C100,"Resolved",
=COUNTIFS(B2:B100,"Open", C2:C100,"High") B2:B100,"Team A")
Count all open tickets with High priority — key SLA monitoring Total resolution hours for Team A's resolved tickets.
formula.

AVERAGEIF / AVERAGEIFS STATISTICAL MAX / MIN / AVERAGE STATISTICAL

Calculate average of cells meeting conditions. Used to find Basic but powerful. Track longest/shortest resolution times,
average handling time (AHT), CSAT scores, or resolution time find outliers, or calculate mean CSAT scores across a period.
by priority/team.
=MAX(range) | =MIN(range) | =AVERAGE(range)
=AVERAGEIF(range, criteria, avg_range)
=AVERAGEIFS(avg_range, range1, crit1)
=MAX(D2:D100) — Longest ticket resolution time
=AVERAGE(E2:E100) — Mean CSAT score for the month
=AVERAGEIF(B2:B100,"P1", D2:D100)
Average resolution time for Priority 1 tickets — directly tied to
SLA metrics.

LOGICAL & CONDITIONAL

IF LOGICAL IFS LOGICAL

Returns one value if a condition is true, another if false. The Evaluates multiple conditions without nesting IF. Perfect for
most-used formula in SDO for tagging SLA breaches, priority priority bucketing (P1/P2/P3) or categorising CSAT scores as
flags, or pass/fail checks. Excellent/Good/Poor.

=IF(logical_test, value_if_true, value_if_false) =IFS(cond1, val1, cond2, val2, TRUE, default)

=IF(D2>8,"SLA Breached","Within SLA") =IFS(E2>=9,"Excellent", E2>=7,"Good",

Flag tickets where resolution exceeded 8 hours as SLA E2>=5,"Neutral", TRUE,"Poor")
Breached. Categorise CSAT score into buckets automatically.

AND / OR LOGICAL IFERROR LOGICAL

Combine multiple conditions inside IF. AND = all must be true; Catches formula errors (#N/A, #DIV/0!, etc.) and replaces
OR = at least one must be true. Use for escalation logic or them with a custom value. Essential for keeping dashboards
complex SLA rules. clean when data is incomplete.

=IF(AND(cond1, cond2), true_val, false_val) =IFERROR(value, value_if_error)

=IF(OR(cond1, cond2), true_val, false_val)

=IFERROR(VLOOKUP(A2,DB!A:C,3,0), "Data Missing")

=IF(AND(B2="P1", D2>4), "Escalate", "Monitor") Show "Data Missing" instead of #N/A when a ticket ID isn't
Escalate only if ticket is P1 AND open beyond 4 hours. found.

T E X T M A N I P U L AT I O N

CONCATENATE / CONCAT / & TEXT LEFT / RIGHT / MID TEXT

Join text from multiple cells. Useful for creating ticket IDs, full Extract portions of text. LEFT from start, RIGHT from end, MID
agent names, or combining region + team codes in reports. from any position. Useful for extracting codes or IDs from
longer strings.
=CONCAT(text1, text2) or =A2&" - "&B2
=LEFT(text, num_chars)
=MID(text, start, num_chars)
=A2&"-"&B2 → "TKT-2024-001"
Combine ticket prefix + year + number into a unique ID.
=LEFT(A2, 3) extracts "TKT" from "TKT-4521"
=MID(A2, 5, 4) extracts "4521" — the ticket number portion.

TRIM / UPPER / LOWER / PROPER TEXT LEN / FIND / SUBSTITUTE TEXT

Clean messy data. TRIM removes extra spaces; LEN counts characters; FIND locates a character's position;
UPPER/LOWER/PROPER fix capitalization. Critical for data SUBSTITUTE replaces text. Used in data cleaning and parsing
standardisation before running reports. multi-value fields.

=TRIM(text) | =UPPER(text) | =PROPER(text) =LEN(text) | =FIND(find_text, within) | =SUBSTITUTE

=PROPER(TRIM(A2)) =SUBSTITUTE(A2, "CLOSED", "RESOLVED")

Converts " john doe " to "John Doe" — standardise agent name Bulk-replace outdated status labels in a ticket dump.
fields.

D AT E & T I M E

TODAY / NOW DATE DATEDIF / DAYS DATE

TODAY() returns current date; NOW() includes time. Used as Calculate difference between two dates in days, months, or
dynamic baselines for aging calculations and SLA countdown years. DATEDIF is the classic; DAYS is simpler. Key for SLA
timers. breach calculations and tenure tracking.

=TODAY() | =NOW() =DATEDIF(start, end, "D") | =DAYS(end, start)

=TODAY()-A2 =DATEDIF(B2, C2, "D")

Calculate how many days a ticket has been open (ticket age) — Days between ticket creation and resolution — core SLA metric.
used in aging reports.

NETWORKDAYS DATE TEXT (Date Formatting) DATE

Counts working days (excluding weekends and optional Convert dates into readable strings for reports. Especially
holidays) between two dates. More accurate than DAYS for SLA useful when building weekly/monthly summary labels
calculations in real business environments. dynamically.

=NETWORKDAYS(start_date, end_date, [holidays]) =TEXT(date, "format_code")

=NETWORKDAYS(B2, C2, Holidays!A:A) =TEXT(A2,"MMM-YYYY") → "Jan-2025"

Business days for resolution — avoids counting weekends in =TEXT(A2,"dddd") → "Monday" — extract day name from a
SLA breach checks. date.

💡 Interview Tip: Interviewers love practical examples. For every formula, be ready to describe a real SDO scenario — like using
COUNTIFS to count P1 SLA breaches, or NETWORKDAYS to calculate resolution time excluding weekends. Also practice Pivot
Tables and Conditional Formatting alongside formulas — they're equally expected in operations roles.