Formula error fixes
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, with a live example and an exercise.
How to fix VLOOKUP #N/A caused by extra spaces
=XLOOKUP(G1,TRIM(A2:A9),C2:C9)Trim the lookup column inside the formula: =XLOOKUP(G1,TRIM(A2:A9),C2:C9). Better still, clean the column once with TRIM.
How to fix VLOOKUP #N/A with numbers stored as text
=VLOOKUP(F1&"",A2:C7,2,FALSE)Make both sides the same type. When the table holds text IDs, turn the lookup number into text: =VLOOKUP(F1&"",A2:C7,2,FALSE).
How to fix VLOOKUP #N/A when IDs have leading zeros
=XLOOKUP(TEXT(F1,"00000"),A2:A7,C2:C7)Pad the lookup value into the same text format as the table: =XLOOKUP(TEXT(F1,"00000"),A2:A7,C2:C7).
How to fix VLOOKUP #N/A errors after filling the formula down
=VLOOKUP(A2,$D$2:$E$6,2,FALSE)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.
Why does VLOOKUP return the wrong value in Excel?
=VLOOKUP(F1,A2:C9,3,FALSE)The fourth argument is missing. Without FALSE, VLOOKUP does an approximate match on unsorted data: =VLOOKUP(F1,A2:C9,3,FALSE).
Why does an approximate-match VLOOKUP return #N/A?
=XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4,0,-1)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.
Why does INDEX MATCH return the wrong value or #N/A?
=INDEX(C2:C8,MATCH(F1,A2:A8,0))Give MATCH a 0 for an exact match and start both ranges on the same row: =INDEX(C2:C8,MATCH(F1,A2:A8,0)).
How to fix the #VALUE! error in XLOOKUP
=XLOOKUP(G1,A2:A9,C2:C9)Make the lookup and return ranges the same size: =XLOOKUP(G1,A2:A9,C2:C9), not A2:A9 with C2:C8.
How to fix the #REF! error in VLOOKUP
=VLOOKUP(G1,A2:D9,4,FALSE)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).
How to stop VLOOKUP from breaking when columns are inserted or deleted
=VLOOKUP(H1,A2:E8,MATCH(H2,A1:E1,0),FALSE)Find the column number from its header instead of typing it: =VLOOKUP(H1,A2:E8,MATCH(H2,A1:E1,0),FALSE).
Why does VLOOKUP return 0 instead of a blank?
=LET(p,XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6),IF(p="","",p))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)).
Why does VLOOKUP return #N/A when looking up a date?
=XLOOKUP(E1,INT(A2:A8),B2:B8)The column holds dates with times, so the bare date never matches exactly. Strip the time: =XLOOKUP(E1,INT(A2:A8),B2:B8).
Why don't wildcards work in XLOOKUP?
=XLOOKUP("*"&E1&"*",A2:A7,B2:B7,"Not found",2)XLOOKUP treats * and ? as plain characters unless you set match mode 2: =XLOOKUP("*"&E1&"*",A2:A7,B2:B7,"Not found",2).
What is the difference between IFERROR and IFNA in Excel?
=IFNA(VLOOKUP(A2,$E$2:$F$5,2,FALSE),0)*B2IFNA 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.
Why doesn't VLOOKUP find rows added to the bottom of the table?
=XLOOKUP(G1,A:A,C:C)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).
How to check if a cell contains an error in Excel
=IF(ISERROR(D2),"Fix row","OK")Use ISERROR inside IF: =IF(ISERROR(D2),"Fix row","OK"). It is TRUE for #N/A, #VALUE!, #DIV/0! and every other error.
How to fix #VALUE! when adding cells in Excel
=SUM(B2:D2)Use SUM instead of +: =SUM(B2:D2) skips text such as "n/a", while =B2+C2+D2 returns #VALUE!.
How to fix #VALUE! when subtracting dates in Excel
=C2-DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2))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)).
How to fix dates stored as text in Excel
=DATEVALUE(A2)Convert them with DATEVALUE and format the result as a date: =DATEVALUE(A2).
Why does DATEVALUE return #VALUE!?
=IF(ISNUMBER(A2),A2,DATEVALUE(A2))DATEVALUE only converts text, and the cell already holds a real date. Check first: =IF(ISNUMBER(A2),A2,DATEVALUE(A2)).
Why are my dates not sorting correctly in Excel?
=SORTBY(A2:C8,DATEVALUE(A2:A8))The dates are text, so they sort character by character. Sort by the converted dates: =SORTBY(A2:C8,DATEVALUE(A2:A8)).
Why does MAX return 0 for a column of dates?
=MAX(DATEVALUE(B2:B7))The dates are text, and MAX ignores text. Convert them inside the formula: =MAX(DATEVALUE(B2:B7)).
How to fix #VALUE! from SEARCH or FIND when the text isn't found
=IFERROR(LEFT(A2,SEARCH(" - ",A2)-1),A2)Catch the "not found" case: =IFERROR(LEFT(A2,SEARCH(" - ",A2)-1),A2) returns the whole text when there is no separator.
Why does SUMPRODUCT return #VALUE! with text in the range?
=SUMPRODUCT(B2:B7,C2:C7)Multiplying with * fails on text. Give the ranges as separate arguments, which treats text as 0: =SUMPRODUCT(B2:B7,C2:C7).
How to fix #VALUE! in SUMIFS
=SUMIFS(C2:C8,B2:B8,F1)Make every range the same size: =SUMIFS(C2:C8,B2:B8,F1), not C2:C8 with B2:B9.
Why does VALUE return #VALUE! for numbers with units?
=VALUE(SUBSTITUTE(A2," kg",""))VALUE can't read letters. Remove the unit first: =VALUE(SUBSTITUTE(A2," kg","")).
How to fix #DIV/0! in AVERAGEIF
=IF(COUNTIF($B$2:$B$7,E2)=0,"No sales",AVERAGEIF($B$2:$B$7,E2,$C$2:$C$7))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)).
How to fix #DIV/0! in a percent change formula
=IF(B2=0,"New",(C2-B2)/B2)Handle a starting value of 0 separately: =IF(B2=0,"New",(C2-B2)/B2).
How to fix #REF! after deleting a column in Excel
=SUM(B2:E2)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!.
How to fix the #NAME? error in Excel
=AVERAGE(B2:B9)#NAME? means a word in the formula isn't recognized, usually a misspelled function: =AVERGE(B2:B9) fails, =AVERAGE(B2:B9) works.
Why does my IF formula with text return #NAME?
=IF(C2="Yes","Approved","Waiting")Text in a formula needs double quotes. =IF(C2=Yes,Approved,Waiting) fails; =IF(C2="Yes","Approved","Waiting") works.
Why does XLOOKUP show #NAME? and what can I use instead?
=INDEX(C2:C9,MATCH(G1,A2:A9,0))Your Excel predates XLOOKUP, which arrived in Excel 2021 and Microsoft 365. Use INDEX and MATCH: =INDEX(C2:C9,MATCH(G1,A2:A9,0)).
How to fix the #SPILL! error in Excel
=UNIQUE(B2:B8)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!.
Why does VLOOKUP return #SPILL! in Excel 365?
=VLOOKUP(B2,$E$2:$F$4,2,FALSE)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).
Why does Excel add @ to my formula?
=B2:B7*C2:C7@ 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.
Why does DATEDIF return #NUM!?
=DATEDIF(B2,IF(C2="",$G$1,C2),"y")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").
Why does LARGE return #NUM! for a top 5 list?
=IF(D2>COUNT($B$2:$B$6),"",LARGE($B$2:$B$6,D2))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)).
How to fix a circular reference in a SUM total
=SUM(B2:B8)The total includes its own cell. End the range above it: =SUM(B2:B8) in B9, not =SUM(B2:B9).
How to calculate a fee that is part of the total without a circular reference
=B2/(1-$F$1)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.
Why does SUM return 0 in Excel?
=SUMPRODUCT(--B2:B8)The numbers are stored as text, and SUM skips text. Convert them inside the formula: =SUMPRODUCT(--B2:B8).
Why does SUMIF return 0 when the text looks the same?
=SUMPRODUCT((TRIM(B2:B8)=F1)*C2:C8)The data has hidden spaces, so "East " doesn't equal "East". Trim inside the formula: =SUMPRODUCT((TRIM(B2:B8)=F1)*C2:C8).
Why is COUNTIF not counting cells with extra spaces?
=SUMPRODUCT(--(TRIM(B2:B9)=E1))COUNTIF compares whole cells, so " billing" isn't "billing". Trim inside the count: =SUMPRODUCT(--(TRIM(B2:B9)=E1)).
How to make COUNTIF case-sensitive in Excel
=SUMPRODUCT(--EXACT(A2:A8,D1))COUNTIF ignores case, so use EXACT inside SUMPRODUCT: =SUMPRODUCT(--EXACT(A2:A8,D1)).
Why does COUNTIF return the wrong count for numbers stored as text?
=SUMPRODUCT(--(--B2:B9>E1))COUNTIF's ">100" skips text. Convert inside the count: =SUMPRODUCT(--(--B2:B9>E1)).
Why does COUNTIFS miss the last day of a date range?
=COUNTIFS(A2:A8,">="&E1,A2:A8,"<"&E2+1)The dates have times, so "<="&end stops at midnight. Count up to the next day instead: =COUNTIFS(A2:A8,">="&E1,A2:A8,"<"&E2+1).
Why does SUMIFS return 0 with two criteria on the same column?
=SUM(SUMIFS(C2:C8,B2:B8,E2:E3))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)).
Why does ISBLANK return FALSE for a cell that looks empty?
=IF(LEN(TRIM(B2))=0,"Empty","Has data")The cell holds a formula returning "" or a space. Test what it shows instead: =IF(LEN(TRIM(B2))=0,"Empty","Has data").
Why does COUNTIF return 0 when the criteria refers to a cell?
=COUNTIF(B2:B8,">="&E1)The cell reference is inside the quotes. Join the operator to the cell with &: =COUNTIF(B2:B8,">="&E1), not ">=E1".
Why is my formula showing as text instead of the result?
=B2*C2The cell was formatted as Text, or the formula starts with a space or apostrophe. Set the cell to General and re-enter =B2*C2.
Why does my percentage show as a decimal in Excel?
=B2/C2A percentage is stored as a fraction (0.75). Keep the formula =B2/C2 and apply the % number format; don't multiply by 100.
Why does my date show as a number in Excel?
=B2+C2Dates 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.
Why does a date turn into a number when joined with text?
=B2&" is due on "&TEXT(C2,"mmm d, yyyy")Joining uses the date's serial number. Format it with TEXT first: =B2&" is due on "&TEXT(C2,"mmm d, yyyy").
Why doesn't my total match the sum of the rounded numbers?
=ROUND(B2*$F$1,2)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.
Why does my IF formula return FALSE?
=IF(B2>=$F$1,"Bonus","")The third argument is missing, so IF returns FALSE when the test fails. Give it one: =IF(B2>=$F$1,"Bonus","").
Why does IFS return #N/A when no condition is met?
=IFS(B2<0,"Overdue",B2<=1,"Urgent",B2<=7,"This week",TRUE,"Later")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").
Why does IF say a number stored as text is greater than 100?
=IF(--B2>=$F$1,"Bulk","Standard")Text always ranks above numbers in a comparison. Convert first: =IF(--B2>=$F$1,"Bulk","Standard").
Why is my IF formula not matching text that looks the same?
=IF(TRIM(B2)="Paid","Closed","Chase")The cell has hidden spaces, so "Paid " isn't "Paid". Trim it in the test: =IF(TRIM(B2)="Paid","Closed","Chase").
Why does my formula give wrong results when copied down?
=B2-$F$1A reference that should stay fixed moves as you copy. Lock it with $: =B2-$F$1.
Why does my nested IF return the wrong result?
=IF(B2>20,"Freight",IF(B2>5,"Large",IF(B2>1,"Standard","Letter")))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"))).
Why does IF fail on a value that displays as the pass mark?
=IF(ROUND(B2,0)>=$F$1,"Pass","Fail")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").
Why does an overnight time difference show ##### or a negative number?
=MOD(C2-B2,1)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).
Why does a sum of hours reset after 24 hours?
=SUM(B2:B6)*24The 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.
Why does Excel show 1/0/1900 instead of a blank?
=IF(B2="","",B2+14)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).