Share this content
Excel #N/A lookup errors fixed

Quick fixes for Excel #N/A lookup and MATCH errors

by

In the world of Excel, the characters “#N/A” can strike fear into the hearts of even experienced users when trying to match or reference data in other cells.

20th May 2021
Share this content

Contextures blogger Debra Dalgleish recently published a collection of quick fixes for #N/A errors when running a MATCH formula.

As long-time AccountingWEB Excel tutor David Ringstrom noted on Twitter, “These same issues trip folks up with VLOOKUP, XLOOKUP, SUMIF, SUMIFS, and other functions as well.”

According to Dalgleish, one of the most common causes for MATCH errors is when one of the values is a number and the other is text – typically when importing or copying data into Excel from sources such as bank statements.

Running a quick Sum or AutoSum calculation on an imported data column will usually produce the tell-tale sign that the figures in are text, because the Sum total will add up to 0.

Another way to check is to click an imported cell with a numeric figure and look at the formula bar; an apostrophe indicates that the cell contents are text rather than numbers.

To explain the different potential causes of MATCH #N/A problems, the Excel tutor created a simple lookup worksheet. In the example below the yellow cells contain text, and the blue cells have real numbers.

Excel MATCH function broken by text-figure incompatibility

The MATCH formula in cell G9 “=MATCH(F9,$G$4:$G$7,0)” produces an #N/A error because the real number in cell F9 doesn't match the text number 123, in the lookup table, cell G4.

Paste Special-Add

Dalgleish has two quick fixes to deal with such situations. The first is to select and copy a blank cell and paste it over the text numbers using the Paste Special-Add option. “It’ll add a zero to everything, but will change them from text to numbers,” she explained.

To follow this sequence, click in the blank cell, select Copy, then select the cells you want to fix. Click the Paste icon on the menu ribbon or right-click to get the Paste Special menu. Ignore the icons and go for the text Paste Special option at the bottom of the menu and then tick the Add option halfway down the dialogue box under the Operation heading.

According to Dalgleish, this operation adds zero to the text figures in the selected cells and changes them all to numbers, which should now add up correctly.

Tweak the formula

The other quick way to convert text to numbers in a lookup is to add an empty string ("") in the formula, after the lookup value. That changes a real number to a text number, so it will find a match in the lookup table.

Here is the formula in cell G10, where F10 is a number, and the MATCH formula works correctly:

=MATCH(F10 & "",$G$4:$G$7,0)

Of course, the cause of unwanted lookup errors might not be so simple. In a companion video, Dalgleish walks viewers through a range of other potential fixes, including techniques to convert numbers to text (as opposed to the text-to-numbers processes described here).

Extraneous spaces that defeat lookup and match formulas can be eliminated using the TRIM function as part of the formula argument, or SUBSTITUTE to replace unwanted HTML character codes that show up in your data. Macros can be created to automate these fixes, including a TRIM macro developed by David McRitchie.

Dalgleish has compiled a MATCH function sample workbook to illustrate how these fixes work, available from the INDEX and MATCH page on her Contextures site.

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.