Lookup & Reference
Map Likert Survey Text Labels to Numeric Scores (SWITCH vs XLOOKUP Table)
Convert qualitative survey responses ("Strongly Agree" through "Strongly Disagree") or risk ratings into numeric 1–5 weights using SWITCH or an XLOOKUP mapping table, while excluding "N/A" responses from averages.
Turn qualitative Likert text scales into clean numeric analytics!
Employee engagement polls, CSAT surveys, and vendor audits export qualitative text strings—"Strongly Agree", "Agree", "Neutral", "Disagree", "Strongly Disagree", plus "Prefer Not to Answer"—that AVERAGE and SUM cannot aggregate directly! Writing five nested IF statements is brittle and unreadable.
For a fixed 5-point scale, SWITCH(TRIM(B2), "Strongly Agree", 5, "Agree", 4, "Neutral", 3, "Disagree", 2, "Strongly Disagree", 1, "") maps every valid label cleanly in one flat expression and returns "" (empty text) for skipped or "N/A" answers—so AVERAGE(C2:C6) automatically ignores non-responses instead of dragging the mean down with zeros! When scales change across surveys, store the mapping in a 2-column lookup range E2:F6 and use XLOOKUP(TRIM(B2), $E$2:$E$6, $F$2:$F$6, "")!
| Survey Response (B2) | SWITCH / XLOOKUP Score | Included in AVERAGE? | Why "" Beats 0 for Skipped |
|---|---|---|---|
| "Strongly Agree" / "Agree" | 5 / 4 | Yes | Numeric weights aggregated cleanly |
| "Neutral" / "Disagree" | 3 / 2 | Yes | Exact case-insensitive match |
| "N/A" or Blank "" | "" (Empty Text) | Ignored by AVERAGE! | Returning 0 would falsely depress CSAT! |
Map 5-Point Likert Text to 1–5 Numeric Score via SWITCH
Trims accidental spaces and maps the 5 standard Likert labels to 5..1, returning "" for blank or non-applicable entries.
=SWITCH(TRIM(B2), "Strongly Agree", 5, "Agree", 4, "Neutral", 3, "Disagree", 2, "Strongly Disagree", 1, "")Table-Driven Score Mapping via XLOOKUP (Audit-Friendly)
Looks up TRIM(B2) against scale labels E2:E6 and returns numeric weights F2:F6 (or "" when unmapped).
=XLOOKUP(TRIM(B2), $E$2:$E$6, $F$2:$F$6, "")Try the example
Use the original values or change them to test your understanding.