<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: longtermswiv</title>
    <description>The latest articles on DEV Community by longtermswiv (@longtermswiv).</description>
    <link>https://dev.to/longtermswiv</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4250200%2F405c53bd-45cc-479f-a9c8-0217913c00f7.png</url>
      <title>DEV Community: longtermswiv</title>
      <link>https://dev.to/longtermswiv</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/longtermswiv"/>
    <language>en</language>
    <item>
      <title>The 8 Excel errors, explained: what #N/A, #VALUE! and #REF! actually mean</title>
      <dc:creator>longtermswiv</dc:creator>
      <pubDate>Tue, 17 Nov 2026 17:03:28 +0000</pubDate>
      <link>https://dev.to/longtermswiv/the-8-excel-errors-explained-what-na-value-and-ref-actually-mean-3e0k</link>
      <guid>https://dev.to/longtermswiv/the-8-excel-errors-explained-what-na-value-and-ref-actually-mean-3e0k</guid>
      <description>&lt;p&gt;Every Excel user has stared at a cell full of &lt;code&gt;#&lt;/code&gt; symbols and wondered what went wrong. The eight error values look cryptic, but each one is a precise diagnostic message. Once you can read them, debugging a spreadsheet stops being guesswork.&lt;/p&gt;

&lt;p&gt;Here is what each error actually means, the most common cause, and the fix — verified against a real formula engine, not just folklore.&lt;/p&gt;

&lt;h2&gt;
  
  
  #N/A — "I looked, and it isn't there"
&lt;/h2&gt;

&lt;p&gt;&lt;code&gt;#N/A&lt;/code&gt; means &lt;em&gt;not available&lt;/em&gt;. A lookup function searched for a value and did not find it.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=VLOOKUP("banana", A2:B10, 2, FALSE)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;If "banana" is not in the first column of &lt;code&gt;A2:B10&lt;/code&gt;, you get &lt;code&gt;#N/A&lt;/code&gt;. This is not a bug — it is the function correctly reporting "no match."&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Fix:&lt;/strong&gt; confirm the lookup value exists and is spelled identically (trailing spaces are a classic culprit — wrap the lookup value in &lt;code&gt;TRIM&lt;/code&gt;). If a missing match is &lt;em&gt;expected&lt;/em&gt;, handle it gracefully:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IFNA(VLOOKUP("banana", A2:B10, 2, FALSE), "not found")
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;IFNA&lt;/code&gt; catches only &lt;code&gt;#N/A&lt;/code&gt;, which is usually what you want for lookups. &lt;code&gt;IFERROR&lt;/code&gt; catches everything and can hide real mistakes.&lt;/p&gt;

&lt;h2&gt;
  
  
  #VALUE! — "wrong kind of thing"
&lt;/h2&gt;

&lt;p&gt;&lt;code&gt;#VALUE!&lt;/code&gt; means a formula received the wrong &lt;em&gt;type&lt;/em&gt; of argument — usually text where a number was expected.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;="abc" + 1
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;You cannot add a number to text. The same happens when a cell that looks like a number is actually stored as text (common after importing CSVs).&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Fix:&lt;/strong&gt; coerce text to numbers with &lt;code&gt;VALUE()&lt;/code&gt;, or check for stray characters. A leading apostrophe or a non-breaking space can make a "number" into text.&lt;/p&gt;

&lt;h2&gt;
  
  
  #REF! — "the cell you pointed at is gone"
&lt;/h2&gt;

&lt;p&gt;&lt;code&gt;#REF!&lt;/code&gt; means an invalid cell reference. It almost always appears &lt;em&gt;after&lt;/em&gt; you delete a row, column, or sheet that a formula referred to.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=Sheet2!A1   ' then you delete Sheet2
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Fix:&lt;/strong&gt; &lt;code&gt;#REF!&lt;/code&gt; cannot be recovered by editing the formula alone — the referent is gone. Undo the deletion if you can, otherwise rebuild the reference. This is why hard-coded cross-sheet references are fragile; structured tables and named ranges survive deletions better.&lt;/p&gt;

&lt;h2&gt;
  
  
  #DIV/0! — "you divided by nothing"
&lt;/h2&gt;

&lt;p&gt;&lt;code&gt;#DIV/0!&lt;/code&gt; means division by zero (or by an empty cell, which Excel treats as zero).&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=A1/B1   ' B1 is 0 or blank
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Fix:&lt;/strong&gt; guard the denominator:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(B1=0, 0, A1/B1)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;For averages over a range that might be empty, &lt;code&gt;AVERAGEIF&lt;/code&gt; or &lt;code&gt;IFERROR&lt;/code&gt; keeps the result clean.&lt;/p&gt;

&lt;h2&gt;
  
  
  #NAME? — "I don't recognise that word"
&lt;/h2&gt;

&lt;p&gt;&lt;code&gt;#NAME?&lt;/code&gt; means Excel does not recognise text in the formula — usually a misspelled function name, a missing add-in, or an undefined named range.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=VLOKUP(A1, B:C, 2, FALSE)   ' typo: VLOKUP
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Fix:&lt;/strong&gt; check spelling. If the function is real but still errors (e.g. &lt;code&gt;XLOOKUP&lt;/code&gt; on an old Excel version), your version does not support it. Undeclared named ranges also trigger this.&lt;/p&gt;

&lt;h2&gt;
  
  
  #NUM! — "a number went out of bounds"
&lt;/h2&gt;

&lt;p&gt;&lt;code&gt;#NUM!&lt;/code&gt; means a numeric argument is invalid — a negative number where only positive works, or a result too large to represent.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=SQRT(-1)
=IRR(A1:A5)   ' no convergence
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Fix:&lt;/strong&gt; validate inputs (e.g. &lt;code&gt;SQRT&lt;/code&gt; needs a non-negative number). Iterative functions like &lt;code&gt;IRR&lt;/code&gt; and &lt;code&gt;RATE&lt;/code&gt; need a sensible guess argument to converge.&lt;/p&gt;

&lt;h2&gt;
  
  
  #NULL! — "those ranges don't overlap"
&lt;/h2&gt;

&lt;p&gt;&lt;code&gt;#NULL!&lt;/code&gt; is the rarest. It means you specified an intersection of two ranges that do not intersect — usually a stray space between references.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=SUM(A1:A5 B1:B5)   ' space means intersection; these don't overlap
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Fix:&lt;/strong&gt; you probably meant a comma (union) instead of a space (intersection):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=SUM(A1:A5, B1:B5)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  ##### — "not an error at all"
&lt;/h2&gt;

&lt;p&gt;A cell full of &lt;code&gt;#&lt;/code&gt; is &lt;strong&gt;not an error value&lt;/strong&gt;. It means the column is too narrow to display the number, or a date/time is negative. Widen the column and it resolves.&lt;/p&gt;

&lt;h2&gt;
  
  
  A quick diagnostic table
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Error&lt;/th&gt;
&lt;th&gt;Meaning&lt;/th&gt;
&lt;th&gt;First thing to check&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;#N/A&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;lookup found no match&lt;/td&gt;
&lt;td&gt;spelling / trailing spaces&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;#VALUE!&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;wrong argument type&lt;/td&gt;
&lt;td&gt;text stored as number&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;#REF!&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;reference deleted&lt;/td&gt;
&lt;td&gt;undo the deletion&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;#DIV/0!&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;divide by zero/blank&lt;/td&gt;
&lt;td&gt;guard the denominator&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;#NAME?&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;unrecognised name&lt;/td&gt;
&lt;td&gt;function spelling / version&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;#NUM!&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;number out of bounds&lt;/td&gt;
&lt;td&gt;input validity / guess arg&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;#NULL!&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;ranges don't intersect&lt;/td&gt;
&lt;td&gt;space vs comma&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;#####&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;column too narrow&lt;/td&gt;
&lt;td&gt;widen the column&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  Try them live
&lt;/h2&gt;

&lt;p&gt;I built a free reference where you can trigger each error in a live in-browser spreadsheet and see the fix side by side — no install, no signup: &lt;a href="https://formulalab.dev/errors.html" rel="noopener noreferrer"&gt;Excel error codes reference&lt;/a&gt;. There are also &lt;a href="https://formulalab.dev/" rel="noopener noreferrer"&gt;81 function labs&lt;/a&gt; and a &lt;a href="https://formulalab.dev/practice.html" rel="noopener noreferrer"&gt;free 30-exercise practice workbook&lt;/a&gt; if you want to drill these.&lt;/p&gt;

&lt;p&gt;The takeaway: an Excel error is not a failure, it is a &lt;em&gt;label&lt;/em&gt;. Read the label and the fix is usually one step away.&lt;/p&gt;

</description>
      <category>webdev</category>
      <category>excel</category>
      <category>spreadsheet</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>I benchmarked VLOOKUP vs XLOOKUP vs INDEX/MATCH in a JavaScript spreadsheet engine. The winner surprised me.</title>
      <dc:creator>longtermswiv</dc:creator>
      <pubDate>Tue, 17 Nov 2026 16:00:00 +0000</pubDate>
      <link>https://dev.to/longtermswiv/i-benchmarked-vlookup-vs-xlookup-vs-indexmatch-in-a-javascript-spreadsheet-engine-the-winner-3llk</link>
      <guid>https://dev.to/longtermswiv/i-benchmarked-vlookup-vs-xlookup-vs-indexmatch-in-a-javascript-spreadsheet-engine-the-winner-3llk</guid>
      <description>&lt;p&gt;Every "VLOOKUP vs XLOOKUP" article tells you the same thing: XLOOKUP is newer, cleaner, and faster. I wanted to see actual numbers instead of vibes, so I ran a controlled benchmark inside &lt;a href="https://hyperformula.handsontable.com/" rel="noopener noreferrer"&gt;HyperFormula&lt;/a&gt;, the open-source formula engine that powers a lot of web-based spreadsheets.&lt;/p&gt;

&lt;p&gt;The result inverted the usual advice. &lt;strong&gt;VLOOKUP was the fastest of the three at every data size I tested, and INDEX/MATCH — the "pro" choice — was the slowest.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Here is the data, exactly how I measured it, and the one caveat that keeps this from being a blanket recommendation.&lt;/p&gt;

&lt;h2&gt;
  
  
  The setup
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Engine: HyperFormula &lt;strong&gt;3.3.1&lt;/strong&gt; (GPL-3.0, used here for benchmarking only)&lt;/li&gt;
&lt;li&gt;Runtime: Node v24.22.3, darwin arm64 (Apple Silicon)&lt;/li&gt;
&lt;li&gt;Date: 2026-11-14&lt;/li&gt;
&lt;li&gt;Sheet: a lookup table &lt;code&gt;A1:B{N}&lt;/code&gt; where the key is &lt;code&gt;1..N&lt;/code&gt; and the value is &lt;code&gt;key * 10&lt;/code&gt;. Column D holds &lt;code&gt;M&lt;/code&gt; lookup formulas, each referencing the full column.&lt;/li&gt;
&lt;li&gt;Measurement: time a batch &lt;code&gt;setCellContents&lt;/code&gt; that triggers a full recalculation. Take the &lt;strong&gt;median&lt;/strong&gt; of several runs. Verify correctness by checking that the sum of all results equals &lt;code&gt;sum(1..M) * 10&lt;/code&gt; — so a fast-but-wrong engine cannot win.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Three formulas, same job (find the value for a key):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=VLOOKUP(key, A:B, 2, FALSE)
=INDEX(B:B, MATCH(key, A:A, 0))
=XLOOKUP(key, A:A, B:B)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  The numbers
&lt;/h2&gt;

&lt;p&gt;Median milliseconds for a full recalc, all results verified correct:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Rows (N)&lt;/th&gt;
&lt;th&gt;Lookups (M)&lt;/th&gt;
&lt;th&gt;VLOOKUP&lt;/th&gt;
&lt;th&gt;XLOOKUP&lt;/th&gt;
&lt;th&gt;INDEX/MATCH&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;1,000&lt;/td&gt;
&lt;td&gt;200&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;17.6&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;23.7&lt;/td&gt;
&lt;td&gt;29.0&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;10,000&lt;/td&gt;
&lt;td&gt;500&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;293.6&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;408.5&lt;/td&gt;
&lt;td&gt;536.1&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;50,000&lt;/td&gt;
&lt;td&gt;500&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;2,367&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;3,323&lt;/td&gt;
&lt;td&gt;4,184&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;100,000&lt;/td&gt;
&lt;td&gt;1,000&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;10,870&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;15,046&lt;/td&gt;
&lt;td&gt;18,030&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;At 50,000 rows, VLOOKUP finished in ~2.4s while INDEX/MATCH took ~4.2s — roughly &lt;strong&gt;1.8× slower&lt;/strong&gt; for the identical result. XLOOKUP sat in the middle every time.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why VLOOKUP wins here
&lt;/h2&gt;

&lt;p&gt;This is an implementation detail of the engine, not a law of spreadsheets:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;VLOOKUP has a dedicated, heavily optimized code path.&lt;/strong&gt; It is the most-used lookup function, so engines tend to tune it first. In HyperFormula it evaluates as a single operation with an efficient column scan.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;INDEX/MATCH is two functions.&lt;/strong&gt; MATCH finds the position, INDEX fetches the value. That is two evaluation passes and an intermediate result, which adds overhead per formula — multiplied across hundreds of lookups.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;XLOOKUP is newer and more general&lt;/strong&gt; (it handles left-lookups, defaults, and search modes). Generality costs a little; it does more work per call than the narrow VLOOKUP path.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;The gap widens with row count because the per-lookup overhead is paid &lt;code&gt;M&lt;/code&gt; times against an &lt;code&gt;N&lt;/code&gt;-row scan.&lt;/p&gt;

&lt;h2&gt;
  
  
  The caveat that matters
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;This is HyperFormula's JavaScript engine, not Excel's native calculation engine.&lt;/strong&gt; Excel is written in optimized native code and has its own lookup caching and smart-recalculation logic. The ranking above is about how a &lt;em&gt;web&lt;/em&gt; spreadsheet engine behaves — which is exactly what matters if you are building a spreadsheet-like app, an interactive tutorial, or a formula playground in the browser.&lt;/p&gt;

&lt;p&gt;Do not read this as "always use VLOOKUP in Excel." Read it as: &lt;em&gt;the engine matters more than the function's reputation, and you should measure on your actual target.&lt;/em&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Try it yourself
&lt;/h2&gt;

&lt;p&gt;I built these measurements into a set of free, editable labs. Each page runs the real engine in your browser — change a cell and watch the result recompute, no signup:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;a href="https://formulalab.dev/excel-functions/vlookup.html" rel="noopener noreferrer"&gt;VLOOKUP lab&lt;/a&gt; — syntax, the &lt;code&gt;FALSE&lt;/code&gt; trap, and the benchmark above&lt;/li&gt;
&lt;li&gt;
&lt;a href="https://formulalab.dev/excel-functions/xlookup.html" rel="noopener noreferrer"&gt;XLOOKUP lab&lt;/a&gt; — left-lookups and default values&lt;/li&gt;
&lt;li&gt;
&lt;a href="https://formulalab.dev/excel-functions/index.html" rel="noopener noreferrer"&gt;INDEX&lt;/a&gt; and &lt;a href="https://formulalab.dev/excel-functions/match.html" rel="noopener noreferrer"&gt;MATCH&lt;/a&gt; — the two-step pattern, decomposed&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The full benchmark script (&lt;code&gt;bench.mjs&lt;/code&gt;) and raw JSON results are published alongside the labs so you can reproduce or dispute the numbers. If your engine ranks them differently, I would genuinely like to see it.&lt;/p&gt;

&lt;h2&gt;
  
  
  Takeaway
&lt;/h2&gt;

&lt;p&gt;"XLOOKUP is faster" is a claim, not a measurement. In the engine I tested, the old workhorse won, the flexible newcomer came second, and the expert favorite came last. Benchmark on your target, verify correctness, and let the median decide.&lt;/p&gt;

</description>
      <category>javascript</category>
      <category>performance</category>
      <category>programming</category>
    </item>
  </channel>
</rss>
