# Formula error fixes in Excel and Google Sheets

> Why a formula shows #N/A, #VALUE!, #DIV/0!, #REF!, #NAME? or #SPILL!, or a wrong answer, and the formula that fixes it. 63 answers, each checked by running it.

Source: https://spreadsheetpractice.com/formulas/topic/errors · Updated October 29, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 30, 2026

## Answers

- [How to fix VLOOKUP #N/A caused by extra spaces](https://spreadsheetpractice.com/formulas/vlookup-na-extra-spaces): Trim the lookup column inside the formula: =XLOOKUP(G1,TRIM(A2:A9),C2:C9). Better still, clean the column once with TRIM. `=XLOOKUP(G1,TRIM(A2:A9),C2:C9)`
- [How to fix VLOOKUP #N/A with numbers stored as text](https://spreadsheetpractice.com/formulas/vlookup-na-numbers-stored-as-text): Make both sides the same type. When the table holds text IDs, turn the lookup number into text: =VLOOKUP(F1&"",A2:C7,2,FALSE). `=VLOOKUP(F1&"",A2:C7,2,FALSE)`
- [How to fix VLOOKUP #N/A when IDs have leading zeros](https://spreadsheetpractice.com/formulas/vlookup-na-leading-zeros): Pad the lookup value into the same text format as the table: =XLOOKUP(TEXT(F1,"00000"),A2:A7,C2:C7). `=XLOOKUP(TEXT(F1,"00000"),A2:A7,C2:C7)`
- [How to fix VLOOKUP #N/A errors after filling the formula down](https://spreadsheetpractice.com/formulas/vlookup-na-after-filling-down): Lock the table with $ before you fill: =VLOOKUP(A2,$D$2:$E$6,2,FALSE). Without it, the table range moves down with every row. `=VLOOKUP(A2,$D$2:$E$6,2,FALSE)`
- [Why does VLOOKUP return the wrong value in Excel?](https://spreadsheetpractice.com/formulas/vlookup-returns-wrong-value): The fourth argument is missing. Without FALSE, VLOOKUP does an approximate match on unsorted data: =VLOOKUP(F1,A2:C9,3,FALSE). `=VLOOKUP(F1,A2:C9,3,FALSE)`
- [Why does an approximate-match VLOOKUP return #N/A?](https://spreadsheetpractice.com/formulas/approximate-match-lookup-returns-na): The value is below the first entry in the table. Give that case a result, =XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4,0,-1), or start the table at 0. `=XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4,0,-1)`
- [Why does INDEX MATCH return the wrong value or #N/A?](https://spreadsheetpractice.com/formulas/index-match-returns-wrong-value): Give MATCH a 0 for an exact match and start both ranges on the same row: =INDEX(C2:C8,MATCH(F1,A2:A8,0)). `=INDEX(C2:C8,MATCH(F1,A2:A8,0))`
- [How to fix the #VALUE! error in XLOOKUP](https://spreadsheetpractice.com/formulas/xlookup-value-error): Make the lookup and return ranges the same size: =XLOOKUP(G1,A2:A9,C2:C9), not A2:A9 with C2:C8. `=XLOOKUP(G1,A2:A9,C2:C9)`
- [How to fix the #REF! error in VLOOKUP](https://spreadsheetpractice.com/formulas/vlookup-ref-error): The column number is bigger than the table is wide. Count from the table's first column, or widen the table: =VLOOKUP(G1,A2:D9,4,FALSE). `=VLOOKUP(G1,A2:D9,4,FALSE)`
- [How to stop VLOOKUP from breaking when columns are inserted or deleted](https://spreadsheetpractice.com/formulas/vlookup-breaks-when-columns-are-inserted): Find the column number from its header instead of typing it: =VLOOKUP(H1,A2:E8,MATCH(H2,A1:E1,0),FALSE). `=VLOOKUP(H1,A2:E8,MATCH(H2,A1:E1,0),FALSE)`
- [Why does VLOOKUP return 0 instead of a blank?](https://spreadsheetpractice.com/formulas/vlookup-returns-0-instead-of-blank): A formula that reads an empty cell shows 0. Test the result and return "": =LET(p,XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6),IF(p="","",p)). `=LET(p,XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6),IF(p="","",p))`
- [Why does VLOOKUP return #N/A when looking up a date?](https://spreadsheetpractice.com/formulas/lookup-date-with-time-returns-na): The column holds dates with times, so the bare date never matches exactly. Strip the time: =XLOOKUP(E1,INT(A2:A8),B2:B8). `=XLOOKUP(E1,INT(A2:A8),B2:B8)`
- [Why don't wildcards work in XLOOKUP?](https://spreadsheetpractice.com/formulas/xlookup-wildcards-not-working): XLOOKUP treats * and ? as plain characters unless you set match mode 2: =XLOOKUP("*"&E1&"*",A2:A7,B2:B7,"Not found",2). `=XLOOKUP("*"&E1&"*",A2:A7,B2:B7,"Not found",2)`
- [What is the difference between IFERROR and IFNA in Excel?](https://spreadsheetpractice.com/formulas/iferror-vs-ifna): IFNA replaces only #N/A, the "not found" error; IFERROR replaces every error. Wrap lookups in IFNA so real mistakes still show: =IFNA(VLOOKUP(…),0)*B2. `=IFNA(VLOOKUP(A2,$E$2:$F$5,2,FALSE),0)*B2`
- [Why doesn't VLOOKUP find rows added to the bottom of the table?](https://spreadsheetpractice.com/formulas/vlookup-does-not-find-new-rows): The table range stops above the new rows. Use whole columns, or an Excel Table, so the range grows: =XLOOKUP(G1,A:A,C:C). `=XLOOKUP(G1,A:A,C:C)`
- [How to check if a cell contains an error in Excel](https://spreadsheetpractice.com/formulas/check-if-a-cell-contains-an-error): Use ISERROR inside IF: =IF(ISERROR(D2),"Fix row","OK"). It is TRUE for #N/A, #VALUE!, #DIV/0! and every other error. `=IF(ISERROR(D2),"Fix row","OK")`
- [How to fix #VALUE! when adding cells in Excel](https://spreadsheetpractice.com/formulas/value-error-when-adding-cells): Use SUM instead of +: =SUM(B2:D2) skips text such as "n/a", while =B2+C2+D2 returns #VALUE!. `=SUM(B2:D2)`
- [How to fix #VALUE! when subtracting dates in Excel](https://spreadsheetpractice.com/formulas/value-error-when-subtracting-dates): The dates are text in day/month/year order. Rebuild them with DATE: =C2-DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2)). `=C2-DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2))`
- [How to fix dates stored as text in Excel](https://spreadsheetpractice.com/formulas/fix-dates-stored-as-text): Convert them with DATEVALUE and format the result as a date: =DATEVALUE(A2). `=DATEVALUE(A2)`
- [Why does DATEVALUE return #VALUE!?](https://spreadsheetpractice.com/formulas/datevalue-value-error): DATEVALUE only converts text, and the cell already holds a real date. Check first: =IF(ISNUMBER(A2),A2,DATEVALUE(A2)). `=IF(ISNUMBER(A2),A2,DATEVALUE(A2))`
- [Why are my dates not sorting correctly in Excel?](https://spreadsheetpractice.com/formulas/dates-not-sorting-correctly): The dates are text, so they sort character by character. Sort by the converted dates: =SORTBY(A2:C8,DATEVALUE(A2:A8)). `=SORTBY(A2:C8,DATEVALUE(A2:A8))`
- [Why does MAX return 0 for a column of dates?](https://spreadsheetpractice.com/formulas/max-of-dates-returns-zero): The dates are text, and MAX ignores text. Convert them inside the formula: =MAX(DATEVALUE(B2:B7)). `=MAX(DATEVALUE(B2:B7))`
- [How to fix #VALUE! from SEARCH or FIND when the text isn't found](https://spreadsheetpractice.com/formulas/search-or-find-value-error): Catch the "not found" case: =IFERROR(LEFT(A2,SEARCH(" - ",A2)-1),A2) returns the whole text when there is no separator. `=IFERROR(LEFT(A2,SEARCH(" - ",A2)-1),A2)`
- [Why does SUMPRODUCT return #VALUE! with text in the range?](https://spreadsheetpractice.com/formulas/sumproduct-value-error): Multiplying with * fails on text. Give the ranges as separate arguments, which treats text as 0: =SUMPRODUCT(B2:B7,C2:C7). `=SUMPRODUCT(B2:B7,C2:C7)`
- [How to fix #VALUE! in SUMIFS](https://spreadsheetpractice.com/formulas/sumifs-value-error): Make every range the same size: =SUMIFS(C2:C8,B2:B8,F1), not C2:C8 with B2:B9. `=SUMIFS(C2:C8,B2:B8,F1)`
- [Why does VALUE return #VALUE! for numbers with units?](https://spreadsheetpractice.com/formulas/value-function-error-with-units): VALUE can't read letters. Remove the unit first: =VALUE(SUBSTITUTE(A2," kg","")). `=VALUE(SUBSTITUTE(A2," kg",""))`
- [How to fix #DIV/0! in AVERAGEIF](https://spreadsheetpractice.com/formulas/averageif-div0-error): Check that something matches before averaging: =IF(COUNTIF($B$2:$B$7,E2)=0,"No sales",AVERAGEIF($B$2:$B$7,E2,$C$2:$C$7)). `=IF(COUNTIF($B$2:$B$7,E2)=0,"No sales",AVERAGEIF($B$2:$B$7,E2,$C$2:$C$7))`
- [How to fix #DIV/0! in a percent change formula](https://spreadsheetpractice.com/formulas/percent-change-div0-error): Handle a starting value of 0 separately: =IF(B2=0,"New",(C2-B2)/B2). `=IF(B2=0,"New",(C2-B2)/B2)`
- [How to fix #REF! after deleting a column in Excel](https://spreadsheetpractice.com/formulas/ref-error-after-deleting-a-column): Use a range instead of separate cell references: =SUM(B2:E2) shrinks when a column is deleted, while =B2+C2+D2+E2 turns into #REF!. `=SUM(B2:E2)`
- [How to fix the #NAME? error in Excel](https://spreadsheetpractice.com/formulas/name-error-misspelled-function): #NAME? means a word in the formula isn't recognized, usually a misspelled function: =AVERGE(B2:B9) fails, =AVERAGE(B2:B9) works. `=AVERAGE(B2:B9)`
- [Why does my IF formula with text return #NAME?](https://spreadsheetpractice.com/formulas/if-formula-with-text-returns-name-error): Text in a formula needs double quotes. =IF(C2=Yes,Approved,Waiting) fails; =IF(C2="Yes","Approved","Waiting") works. `=IF(C2="Yes","Approved","Waiting")`
- [Why does XLOOKUP show #NAME? and what can I use instead?](https://spreadsheetpractice.com/formulas/xlookup-name-error-in-older-excel): Your Excel predates XLOOKUP, which arrived in Excel 2021 and Microsoft 365. Use INDEX and MATCH: =INDEX(C2:C9,MATCH(G1,A2:A9,0)). `=INDEX(C2:C9,MATCH(G1,A2:A9,0))`
- [How to fix the #SPILL! error in Excel](https://spreadsheetpractice.com/formulas/spill-error-blocked-range): Clear the cells the result needs to spill into. =UNIQUE(B2:B8) needs E2:E5 empty; anything in the way, even a space, causes #SPILL!. `=UNIQUE(B2:B8)`
- [Why does VLOOKUP return #SPILL! in Excel 365?](https://spreadsheetpractice.com/formulas/vlookup-returns-spill-error): The lookup value is a whole column, so VLOOKUP tries to return a result for every row. Look up one cell and fill down: =VLOOKUP(B2,$E$2:$F$4,2,FALSE). `=VLOOKUP(B2,$E$2:$F$4,2,FALSE)`
- [Why does Excel add @ to my formula?](https://spreadsheetpractice.com/formulas/excel-adds-at-sign-to-formula): @ keeps an older formula returning one value instead of spilling. Remove it to get every result: =B2:B7*C2:C7 spills a line total for each row. `=B2:B7*C2:C7`
- [Why does DATEDIF return #NUM!?](https://spreadsheetpractice.com/formulas/datedif-num-error): The start date is after the end date, usually because the end date is blank. Fill the gap: =DATEDIF(B2,IF(C2="",$G$1,C2),"y"). `=DATEDIF(B2,IF(C2="",$G$1,C2),"y")`
- [Why does LARGE return #NUM! for a top 5 list?](https://spreadsheetpractice.com/formulas/large-returns-num-error): You asked for more places than there are numbers. Leave the extra places blank: =IF(D2>COUNT($B$2:$B$6),"",LARGE($B$2:$B$6,D2)). `=IF(D2>COUNT($B$2:$B$6),"",LARGE($B$2:$B$6,D2))`
- [How to fix a circular reference in a SUM total](https://spreadsheetpractice.com/formulas/circular-reference-in-sum-total): The total includes its own cell. End the range above it: =SUM(B2:B8) in B9, not =SUM(B2:B9). `=SUM(B2:B8)`
- [How to calculate a fee that is part of the total without a circular reference](https://spreadsheetpractice.com/formulas/fee-included-in-total-without-circular-reference): Solve for the total instead of letting the cells refer to each other: =B2/(1-$F$1) is the amount to charge so that the fee comes out of it. `=B2/(1-$F$1)`
- [Why does SUM return 0 in Excel?](https://spreadsheetpractice.com/formulas/sum-returns-zero): The numbers are stored as text, and SUM skips text. Convert them inside the formula: =SUMPRODUCT(--B2:B8). `=SUMPRODUCT(--B2:B8)`
- [Why does SUMIF return 0 when the text looks the same?](https://spreadsheetpractice.com/formulas/sumif-returns-zero): The data has hidden spaces, so "East " doesn't equal "East". Trim inside the formula: =SUMPRODUCT((TRIM(B2:B8)=F1)*C2:C8). `=SUMPRODUCT((TRIM(B2:B8)=F1)*C2:C8)`
- [Why is COUNTIF not counting cells with extra spaces?](https://spreadsheetpractice.com/formulas/countif-not-counting-extra-spaces): COUNTIF compares whole cells, so " billing" isn't "billing". Trim inside the count: =SUMPRODUCT(--(TRIM(B2:B9)=E1)). `=SUMPRODUCT(--(TRIM(B2:B9)=E1))`
- [How to make COUNTIF case-sensitive in Excel](https://spreadsheetpractice.com/formulas/case-sensitive-countif): COUNTIF ignores case, so use EXACT inside SUMPRODUCT: =SUMPRODUCT(--EXACT(A2:A8,D1)). `=SUMPRODUCT(--EXACT(A2:A8,D1))`
- [Why does COUNTIF return the wrong count for numbers stored as text?](https://spreadsheetpractice.com/formulas/countif-numbers-stored-as-text): COUNTIF's ">100" skips text. Convert inside the count: =SUMPRODUCT(--(--B2:B9>E1)). `=SUMPRODUCT(--(--B2:B9>E1))`
- [Why does COUNTIFS miss the last day of a date range?](https://spreadsheetpractice.com/formulas/countifs-misses-last-day-of-date-range): The dates have times, so "="&E1,A2:A8,"<"&E2+1). `=COUNTIFS(A2:A8,">="&E1,A2:A8,"<"&E2+1)`
- [Why does SUMIFS return 0 with two criteria on the same column?](https://spreadsheetpractice.com/formulas/sumifs-or-on-the-same-column): SUMIFS joins its criteria with AND, and a cell can't be East and West at once. Total each and add: =SUM(SUMIFS(C2:C8,B2:B8,E2:E3)). `=SUM(SUMIFS(C2:C8,B2:B8,E2:E3))`
- [Why does ISBLANK return FALSE for a cell that looks empty?](https://spreadsheetpractice.com/formulas/isblank-false-for-empty-looking-cell): The cell holds a formula returning "" or a space. Test what it shows instead: =IF(LEN(TRIM(B2))=0,"Empty","Has data"). `=IF(LEN(TRIM(B2))=0,"Empty","Has data")`
- [Why does COUNTIF return 0 when the criteria refers to a cell?](https://spreadsheetpractice.com/formulas/countif-criteria-with-cell-reference): The cell reference is inside the quotes. Join the operator to the cell with &: =COUNTIF(B2:B8,">="&E1), not ">=E1". `=COUNTIF(B2:B8,">="&E1)`
- [Why is my formula showing as text instead of the result?](https://spreadsheetpractice.com/formulas/formula-showing-as-text): The cell was formatted as Text, or the formula starts with a space or apostrophe. Set the cell to General and re-enter =B2*C2. `=B2*C2`
- [Why does my percentage show as a decimal in Excel?](https://spreadsheetpractice.com/formulas/percentage-shows-as-decimal): A percentage is stored as a fraction (0.75). Keep the formula =B2/C2 and apply the % number format; don't multiply by 100. `=B2/C2`
- [Why does my date show as a number in Excel?](https://spreadsheetpractice.com/formulas/date-shows-as-a-number): Dates are stored as serial numbers, and the result cell has a number format. Keep the formula, like =B2+C2, and format the cell as a date. `=B2+C2`
- [Why does a date turn into a number when joined with text?](https://spreadsheetpractice.com/formulas/date-turns-into-number-when-joined-with-text): Joining uses the date's serial number. Format it with TEXT first: =B2&" is due on "&TEXT(C2,"mmm d, yyyy"). `=B2&" is due on "&TEXT(C2,"mmm d, yyyy")`
- [Why doesn't my total match the sum of the rounded numbers?](https://spreadsheetpractice.com/formulas/total-does-not-match-rounded-values): The cells show rounded values but store every decimal. Round each line with ROUND: =ROUND(B2*$F$1,2), and the total adds what you see. `=ROUND(B2*$F$1,2)`
- [Why does my IF formula return FALSE?](https://spreadsheetpractice.com/formulas/if-formula-returns-false): The third argument is missing, so IF returns FALSE when the test fails. Give it one: =IF(B2>=$F$1,"Bonus",""). `=IF(B2>=$F$1,"Bonus","")`
- [Why does IFS return #N/A when no condition is met?](https://spreadsheetpractice.com/formulas/ifs-returns-na): IFS has nothing to return when every test is FALSE. End it with TRUE and a default: =IFS(B2<0,"Overdue",B2<=1,"Urgent",B2<=7,"This week",TRUE,"Later"). `=IFS(B2<0,"Overdue",B2<=1,"Urgent",B2<=7,"This week",TRUE,"Later")`
- [Why does IF say a number stored as text is greater than 100?](https://spreadsheetpractice.com/formulas/if-greater-than-with-numbers-stored-as-text): Text always ranks above numbers in a comparison. Convert first: =IF(--B2>=$F$1,"Bulk","Standard"). `=IF(--B2>=$F$1,"Bulk","Standard")`
- [Why is my IF formula not matching text that looks the same?](https://spreadsheetpractice.com/formulas/if-not-matching-text-that-looks-the-same): The cell has hidden spaces, so "Paid " isn't "Paid". Trim it in the test: =IF(TRIM(B2)="Paid","Closed","Chase"). `=IF(TRIM(B2)="Paid","Closed","Chase")`
- [Why does my formula give wrong results when copied down?](https://spreadsheetpractice.com/formulas/formula-wrong-when-copied-down): A reference that should stay fixed moves as you copy. Lock it with $: =B2-$F$1. `=B2-$F$1`
- [Why does my nested IF return the wrong result?](https://spreadsheetpractice.com/formulas/nested-if-returns-wrong-result): The tests are in the wrong order: IF stops at the first TRUE. With > tests, check the largest threshold first: =IF(B2>20,"Freight",IF(B2>5,"Large",IF(B2>1,"Standard","Letter"))). `=IF(B2>20,"Freight",IF(B2>5,"Large",IF(B2>1,"Standard","Letter")))`
- [Why does IF fail on a value that displays as the pass mark?](https://spreadsheetpractice.com/formulas/if-fails-on-a-rounded-value): The cell shows a rounded number but stores the decimals, and IF tests the stored value. Round in the test: =IF(ROUND(B2,0)>=$F$1,"Pass","Fail"). `=IF(ROUND(B2,0)>=$F$1,"Pass","Fail")`
- [Why does an overnight time difference show ##### or a negative number?](https://spreadsheetpractice.com/formulas/overnight-time-difference-negative): The end time is "earlier" than the start time. Wrap the subtraction in MOD to roll it into the next day: =MOD(C2-B2,1). `=MOD(C2-B2,1)`
- [Why does a sum of hours reset after 24 hours?](https://spreadsheetpractice.com/formulas/sum-of-hours-resets-after-24): The h:mm format shows only the part of the total under a day. Multiply by 24 for decimal hours, =SUM(B2:B6)*24, or format the total as [h]:mm. `=SUM(B2:B6)*24`
- [Why does Excel show 1/0/1900 instead of a blank?](https://spreadsheetpractice.com/formulas/date-formula-shows-1-0-1900): The formula reads an empty cell as 0, which a date format shows as 1/0/1900. Test for the blank first: =IF(B2="","",B2+14). `=IF(B2="","",B2+14)`

More topics: [Counting formulas](https://spreadsheetpractice.com/formulas/topic/count) · [Sum formulas](https://spreadsheetpractice.com/formulas/topic/sum) · [Lookup formulas](https://spreadsheetpractice.com/formulas/topic/lookup) · [IF and logic formulas](https://spreadsheetpractice.com/formulas/topic/logic) · [Text formulas](https://spreadsheetpractice.com/formulas/topic/text) · [Date formulas](https://spreadsheetpractice.com/formulas/topic/date) · [Dynamic array formulas](https://spreadsheetpractice.com/formulas/topic/dynamic) · [Duplicates and data cleanup formulas](https://spreadsheetpractice.com/formulas/topic/cleanup) · [Rounding and math formulas](https://spreadsheetpractice.com/formulas/topic/math) · [Percentage formulas](https://spreadsheetpractice.com/formulas/topic/percent) · [Time formulas](https://spreadsheetpractice.com/formulas/topic/time) · [Google Sheets formulas](https://spreadsheetpractice.com/formulas/topic/sheets) · [Finance formulas](https://spreadsheetpractice.com/formulas/topic/finance)

Every answer: [Formula answers](https://spreadsheetpractice.com/formulas). Formulas used: `XLOOKUP`, `TRIM`, `ARRAYFORMULA`, `VLOOKUP`, `INDEX`, `MATCH`, `TEXT`, `IFNA`, `COLUMNS`, `LET`, `IF`, `INT`, `IFERROR`, `ISERROR`, `SUM`, `SUMPRODUCT`, `DATE`, `RIGHT`, `MID`, `LEFT`, `DATEVALUE`, `ISNUMBER`, `SORTBY`, `SORT`, `MAX`, `MIN`, `SEARCH`, `SUMIFS`, `SUMIF`, `VALUE`, `SUBSTITUTE`, `COUNTIF`, `AVERAGEIF`, `AVERAGE`, `COUNT`, `UNIQUE`, `DATEDIF`, `LARGE`, `ROUND`, `EXACT`, `COUNTIFS`, `LEN`, `ISBLANK`, `PRODUCT`, `WORKDAY`, `IFS`, `CLEAN`, `MOD`

---

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