Google Sheets IF formula: if this cell, then that, else something else (text, number, blank, AND, OR)

Say which cell to test, what to test for, and what to show when it is true or not. Copy the formula. You can test one cell, add a second test with AND or OR, or fill a whole column in one formula. It runs in your browser and nothing you type here is sent anywhere. Every formula below was run in a real Google Sheet on 3 November 2026 (upper and lower case, blank cells, zero, numbers stored as text, quotes, a divide by zero) once in a US-locale spreadsheet and once in a German-locale one with semicolons.

How to use it

Paste the formula into one cell. For a whole column, paste it in the first row of the result column and leave the cells below it empty, or Sheets shows a #REF! error. Text in the formula goes in double quotes; the builder adds them. A number or a cell like D1 is left without quotes.

Which one to pick

The catches (found by testing in a real Sheet)

• Equal ignores upper and lower case. A2 = "paid" was true for PAID. It was false for Paid with a space at the end.
• Contains: SEARCH ignores case, FIND does not. xxABxx contains "ab" with SEARCH and not with FIND. Tick "match upper and lower case" to get FIND.
• SEARCH treats * and ? as wildcards. Looking for a* in abc returned true. Keep that in mind if your text has either sign.
• Greater than: text counts as bigger than any number. A2 > 5 with the text abc in A2 gave TRUE. A blank cell gave FALSE. Test with "is a number" first if the column mixes both.
• A number typed as text is not a number. ISNUMBER on the text '5 gave FALSE.
• Blank means really empty. A cell with a space in it, or a zero, is not blank: A2 = "" was false for both.
• A whole column and blanks. Without a check, every empty row below your data gets the "not true" answer, all the way down. The builder wraps the formula in IF(B2:B = "", "", ...) so empty rows stay empty. The one exception is "is blank": that test would hide the very rows it looks for, so it fills the empty rows below your data too. Give it a closed range like B2:B100.
• AND and OR do not work on a whole column. The builder multiplies the tests for AND and adds them for OR when you pick the column mode.
• Errors. A divide by zero inside the "show" part gave #DIV/0!. Tick "if the result is an error" to wrap everything in IFERROR.
• Decimal comma. In a German-locale sheet the same formula needs semicolons between arguments; pick the semicolon separator.

No code: KeepSorted

The add-on sorts the original table in place without code: no second copy, so formatting, checkboxes and notes move with their rows and people keep editing the same table. Select the range, pick the sort columns, Save. Free: one area per tab, up to 2 sort columns, no trial clock. Pro ($5/month or $39/year): up to 5 sort columns, custom orders like High > Medium > Low, ticked checkbox rows to the bottom, stacked tables sorted separately, and sorting after form responses, other editors' changes and an hourly check.

More KeepSorted tools and guides

• CONCATENATE formula builder (join cells, TEXTJOIN, row by row)
• SPLIT formula builder (text to columns or rows)
• VLOOKUP formula builder (XLOOKUP, INDEX MATCH, whole column)
• COUNTIF / SUMIF formula builder (count, add up, repeats)
• Running total and percent-of-total formula builder
• RANK formula builder (ties, rank within a group)
• FILTER formula builder (text, number, date conditions)
• Unique values formula maker (UNIQUE, counts, duplicates)
• Sort formula generator (SORT, SORTN, QUERY order by)
• Auto sort script generator (Apps Script that sorts the original)
• StaySorted alternative: an honest comparison

Sources

• Google Docs Editors Help: IF
• Google Docs Editors Help: AND
• Google Docs Editors function list (IFERROR, SEARCH, FIND, ISNUMBER, ARRAYFORMULA, OR)