Error guide
What your formula error means, and how to fix it
Every Excel and Google Sheets error, explained in plain English with the usual causes and a corrected formula.
#N/A
A lookup function (VLOOKUP, XLOOKUP, MATCH, INDEX/MATCH) could not find the value it was looking for.
Common causes
- The value really is missing from the lookup range.
- Extra spaces, for example "Acme " versus "Acme".
- A number stored as text in one list and as a number in the other.
- VLOOKUP without FALSE as the last argument, which makes it do an approximate match on unsorted data.
How to fix it
=IFNA(VLOOKUP(TRIM(A2), Products!$A$2:$C$100, 3, FALSE), "Not found")TRIM removes stray spaces, FALSE forces an exact match and IFNA shows a friendly message.
#VALUE!
A formula got the wrong type of value, most often text where it needs a number or a date.
Common causes
- Cells that look like numbers but are text, e.g. "12 kg" or numbers with a leading apostrophe.
- Dates typed in a format your system does not recognise.
- Adding cells with + when one of them contains text.
How to fix it
=SUM(A2:B2)SUM ignores text, while =A2+B2 fails on it. To convert text to numbers use =VALUE(SUBSTITUTE(A2, " kg", "")).
#REF!
The formula refers to a cell that no longer exists, usually because rows, columns or a sheet were deleted.
Common causes
- Deleting a row or column the formula pointed to.
- Copying a formula so its references move off the edge of the sheet.
- VLOOKUP asking for a column number larger than the table.
How to fix it
=INDEX(Products!$C$2:$C$100, MATCH(A2, Products!$A$2:$A$100, 0))Undo the deletion if you can. INDEX/MATCH survives inserted and deleted columns better than VLOOKUP's column number.
#DIV/0!
A number was divided by zero or by an empty cell.
Common causes
- The divisor cell is empty or 0.
- AVERAGEIF or AVERAGEIFS found no matching rows.
How to fix it
=IF(C2=0, "", B2/C2)Test the divisor first, or wrap the calculation in IFERROR(B2/C2, 0).
#NAME?
Excel does not recognise something in the formula as a function, range name or text.
Common causes
- A misspelled function, e.g. =VLOKUP.
- Text without quotes, e.g. =IF(A2=Yes, …) instead of "Yes".
- A function your version does not have, e.g. XLOOKUP in Excel 2019.
- A named range that was deleted.
How to fix it
=IF(A2="Yes", 1, 0)Put text in double quotes and check that every function exists in your version.
#SPILL!
A dynamic array formula (FILTER, UNIQUE, SORT, SEQUENCE…) has no room to show all its results.
Common causes
- Something is typed in the cells below or to the right.
- The formula is inside an Excel table, where spilling is not allowed.
- Merged cells in the spill area.
How to fix it
=UNIQUE(FILTER(A2:A100, A2:A100<>""))Clear the cells the result needs. Click the formula to see the dashed spill border.
#NUM!
A calculation produced a number that is not valid, or a function could not find a result.
Common causes
- Square root or logarithm of a negative number.
- IRR or RATE could not converge on an answer.
- DATEDIF with a start date after the end date.
How to fix it
=IFERROR(DATEDIF(A2, B2, "d"), "Check dates")Make sure the start date is before the end date.
#NULL!
Two ranges were joined with a space (the intersection operator) but do not overlap.
Common causes
- A missing comma or colon, e.g. =SUM(A1:A10 B1:B10).
How to fix it
=SUM(A1:A10, B1:B10)Separate ranges with a comma (or semicolon in some regions).
Still stuck?
Paste your formula into the fixer and describe what goes wrong. You get the cause and a corrected formula.
Fix my formula