# SWITCH formula generator

> Describe what you need and get a SWITCH formula for Excel and Google Sheets, run on real data before you copy it. Plus 4 tested SWITCH examples.

Source: https://spreadsheetpractice.com/tools/formula-generator/switch · Updated October 29, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

SWITCH compares one value against a list of exact matches and returns the result paired with the first match, with an optional default at the end.

Describe the task at [SWITCH formula generator](https://spreadsheetpractice.com/tools/formula-generator/switch) and get a SWITCH formula for Excel and Google Sheets, tested on real data.

## Tested SWITCH examples

### Use SWITCH instead of nested IFs

`=SWITCH(B2,"N","North","S","South","E","East","W","West","Unknown")`

Excel. Result on the sample sheet: 6 rows (C2:C7). [See it and try it](https://spreadsheetpractice.com/formulas/switch-instead-of-nested-if)

### Look up a value from a list typed into the formula

`=XLOOKUP(B2,{"A","P","C","X"},{"Active","Pending","Closed","Cancelled"},"Unknown")`

Excel. Result on the sample sheet: 6 rows (C2:C7). [See it and try it](https://spreadsheetpractice.com/formulas/lookup-from-array-constant)

### Standardize yes and no values

`=IF(ISNUMBER(MATCH(TRIM(A2),{"y","yes","true"},0)),"Yes",IF(ISNUMBER(MATCH(TRIM(A2),{"n","no","false"},0)),"No","Check"))`

Excel. Result on the sample sheet: 8 rows (B2:B9). [See it and try it](https://spreadsheetpractice.com/formulas/standardize-yes-and-no-values)

### Convert 1.2K and 3M text to numbers

`=IF(RIGHT(A2)="K",LEFT(A2,LEN(A2)-1)*1000,IF(RIGHT(A2)="M",LEFT(A2,LEN(A2)-1)*1000000,VALUE(A2)))`

Excel. Result on the sample sheet: 6 rows (B2:B7). [See it and try it](https://spreadsheetpractice.com/formulas/convert-k-and-m-text-to-numbers)

## Questions

### What does the SWITCH formula generator do?

You describe the task in plain English, and it writes the formula, using SWITCH where that's the right tool, for your table or a sample one. The formula is run in our engine in Excel and Google Sheets mode before you see it, so you get its result, not just a guess.

### What does SWITCH do?

SWITCH compares one value against a list of exact matches and returns the result paired with the first match, with an optional default at the end.

### Can I learn to write SWITCH formulas myself?

Yes. Each example below opens a page where you try the formula in a live grid and it's checked as you go, and the SWITCH function page has a practice exercise.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
