AI how-to notes · 2026-09-18 · last run 2026-09-18 · 한국어 원문

Six places VLOOKUP goes wrong — #N/A, #REF! and the quietly wrong value

One line summary

The product code is plainly there in the table, and you still get #N/A. Reproducing six common causes one at a time on a six-row dummy price list, #N/A came from a trailing space on the value, a number-versus-text mismatch, the lookup column's position and a range that slid while being copied; #REF! came from a bad column index; and leaving out the fourth argument produced no error at all — just the wrong price, 900. That last one, the one with no error, is the dangerous one.

Symptoms

• You can see the product code in the table, but the cell says #N/A
• Some rows resolve fine and only certain rows say #N/A
• You copied the formula down and only a few rows in the middle turned into #N/A
• No error, but the price belongs to a different item

Test environment

The dummy table

The price sheet (단가표) holds six rows. Product code in column A, item name in column B, unit price in column C.

(The columns are product code, item name and unit price; the item names are ballpoint pen, notebook, folder, clip, toner and paper.)

For the employee-number case we also built an employee sheet (사번표). Column A was formatted as text before 1001, 1002 and 1003 went in, so Excel holds those numbers as text.

All six at a glance

⑥ only shows up once the formula is copied, so it gets its own section below.

① One space after the lookup value

Looking up A-102 gives #N/A. On screen it is indistinguishable from A-102. Where the space sits changes how you fix it, so we entered four variants separately.

Leading and trailing spaces are solved by TRIM. A space in the middle is another matter. We entered four more variants to see how TRIM treats the middle.

TRIM strips leading and trailing spaces and collapses runs of spaces in the middle down to one. A single space in the middle survives untouched, so TRIM does not clear that #N/A. To remove interior spaces as well you need SUBSTITUTE to delete every space.

=VLOOKUP(SUBSTITUTE(A2," ",""),단가표!$A$2:$C$7,3,FALSE)

We tried an attached line break too. With a line break (CHAR 10) after A-102, TRIM did not remove it; only wrapping it in TRIM(CLEAN(…)) brought the length down to 5. This is the shape you get from values copied out of a web page or another system.

If the lookup value is in A2, these three lines diagnose it.

=LEN(A2)                    → 6
=LEN(TRIM(CLEAN(A2)))       → 5
=CODE(RIGHT(A2,1))          → 32

Two different LEN results mean TRIM or CLEAN removed or shortened something. Cases where characters were collapsed rather than deleted — like "2 spaces in the middle" above — are caught here too. A CODE of 32 means the last character is a space; 10 means a line break.

② Number on one side, text on the other

The 1002 in the employee sheet is text entered into a text-formatted cell; the 1002 typed into the order sheet is a number. They look identical and give #N/A. We ran the reverse direction as well.

To see which side is text, put ISNUMBER and ISTEXT against both the lookup value and the table value. The fix depends on the direction: if the table is text, wrap the lookup value in TEXT(…,"0"); if the table is numeric, wrap it in VALUE(…).

Employee numbers with a leading zero are not fixed by matching the type alone. For this we built a new test table, separate from the employee sheet used in ②, and put 0123 (홍길동), 0456 (김영수) and 1002 (이하늘) in it as text, then looked them up with the number 123, which has lost its leading zero. The ID 1002 comes up again, but in this table it is tied to 이하늘, a different name from the 김영수 we saw against 1002 in the earlier employee sheet.

TEXT(…,"0") does not bring back a leading zero that is already gone. For a code with a fixed width you have to spell out the width, as in "0000". For codes like this, the better move is to format the input cells as text in the first place.

③ The lookup column is not the first column of the range

VLOOKUP only searches the leftmost column of the range you give it. Set the range to 단가표!$B$2:$C$7 and the leftmost column is the item name (column B), so looking for the code A-102 searches the item names and returns #N/A. Start the range at column A and change the index to 3 and you get 3500.

If your table has the code on the right and the value you want on the left, VLOOKUP over that range cannot fetch the left-hand value at all. Use INDEX and MATCH, or see the XLOOKUP section below.

④ Column index outside the range — a different error, not #N/A

This is the only case that produced #REF!. The error text differs, which makes it useful for narrowing down a cause. We fed seven different column indexes into the same two-column range.

If you see #REF!, start by counting the columns in the range. The index has to be at least 1 and no more than the number of columns in the range. Zero or a negative number branches off to #VALUE!.

A formula that was correct when written can also turn into #REF! later. We took a four-column table (code, item name, spec, price), put =VLOOKUP("A-102",A2:D4,4,FALSE) in, got 3500, then deleted the spec column (column C).

지우기 전: =VLOOKUP("A-102",A2:D4,4,FALSE)  → 3500
C열 지운 뒤: =VLOOKUP("A-102",A2:C4,4,FALSE) → #REF!

(The two lines read "before deleting" and "after deleting column C".)

The range shrank to three columns while the index stayed at 4. When you delete a column from a table, check the column indexes in every formula that references it.

⑤ Drop the fourth argument and another item's price arrives with no error

Leaving off the final FALSE, as in =VLOOKUP(B6,단가표!$A$2:$C$7,3), switches to approximate match. We looked up B-203, which is not in the table, and got 900 with no error. That is the price of B-202 (집게, the clip). A code that does not exist quietly picked up the price of the row above it.

Approximate match works on the assumption that the table is sorted in ascending order. We ran the same eight values against a sorted table and a scrambled one.

Five of the eight rows were wrong on the scrambled table. One of them, C-301, is a code that sits in the table perfectly well, and it returned 3500 (the notebook price) instead of 68000. There is no error marker, so without a cross-check it goes out as-is. If the job is to find only exact matches, always write FALSE or 0 as the fourth argument.

⑥ Copying without locking the range with $

We put =VLOOKUP(A11,단가표!A2:C7,3,FALSE) in the first row and copied it down four rows. The range slid down one row per pasted row, and two of the five rows became #N/A.

If the lookup value happens to still be inside the slid range you get the right answer; values that dropped off the top of the range become #N/A. Three rows look fine, so a quick scan of the table misses it. Clicking an #N/A cell and checking whether the range in the formula bar matches the first row is the fastest check.

We also confirmed a case where the same broken formula does nothing at all. Putting the orders in the same sequence as the price table, starting at A-101, and copying without locking the range, all five rows came out right.

The range slid on every row and every answer was still correct, because the lookup value happened to sit on the first row of the range each time. A correct value does not mean a correct formula. Reorder the orders so a code falls outside the slid range and the #N/A appears then.

The second argument gets both row and column locked, as in $A$2:$C$7. What exactly the $ pins depends on where you put it, and Keeping the reference cell fixed when you copy a formula compares the variants by their resulting values.

Where to look first when #N/A shows up

With the lookup value in A33, we put eight lines in the neighbouring cells. These are the results for A-102 (trailing space).

=COUNTIF(표!$A$2:$A$7,A33)              → 0
=COUNTIF(표!$A$2:$A$7,"*"&TRIM(A33)&"*") → 1
=LEN(A33)                                → 6
=LEN(TRIM(CLEAN(A33)))                   → 5
=CODE(RIGHT(A33,1))                      → 32
=EXACT(A33,표!$A$3)                      → FALSE
=ISNUMBER(A33)                           → FALSE
=IFNA(VLOOKUP(A33,표!$A$2:$B$7,2,FALSE),"코드없음") → 코드없음

(표 is the lookup table sheet, and 코드없음 is the fallback text, "code not found".)

In the trailing-space case, the exact COUNTIF was 0 and the wildcard partial match was 1. A split like that is a signal that something similar exists in the table, but a partial match also hits other, longer codes in the table, so it alone cannot pin the cause. In order:

1. Count how many are in the table with COUNTIF. If it is 0, suspect the lookup value first.
2. Compare LEN against LEN(TRIM(CLEAN(…))) to find invisible characters.
3. Put ISNUMBER and ISTEXT on both the lookup value and the table value to see whether the types match. Do this step regardless of what step 1 said.
4. In the formula bar, check that the leftmost column of the range is the lookup value's column, and that the column index is at least 1 and no more than the number of columns in the range.

To keep #N/A out of a report, wrap it in IFNA. But wrapping before you fix the cause only turns a wrong row into "코드없음", so wrap it only after you have worked through the causes.

XLOOKUP was present in this build

=XLOOKUP("A-102",단가표!$A$2:$A$7,단가표!$C$2:$C$7,"없음") returned 3500. This Excel 16.0 build 20326.0 has XLOOKUP. The formula names the lookup column ($A$2:$A$7) and the return column ($C$2:$C$7) separately. There is no column-index argument at all, and we did not specify a match mode either. The two arguments that produced ③④⑤ simply do not exist in this shape.

With no match mode specified, looking up the missing code B-203 returned 「없음」 (the "not found" text we supplied in the fourth position). That differs from VLOOKUP without a fourth argument, which returned 900. Because the text written in the fourth position comes back as-is, there is no need for a separate IFNA.

Some things it does not fix. We re-ran ① and ② through XLOOKUP. For the ID side we reused the leading-zero test table above (D/E columns, where 1002 = 이하늘), not the original employee sheet.

① and ②, where the values themselves are mismatched, survive a change of function. The values are what has to be fixed.

What failed

• Exporting the first PNG with wb.ExportAsFixedFormat produced a PDF of 단가표, the first sheet, instead of the test sheet. We switched to Activate() on the sheet followed by ws.ExportAsFixedFormat and exported again. The render log is written once when the script ends and is overwritten on every re-run, so what remains in 02_png_렌더로그.txt is the output from after the fix.
• Running a fact-check pass with GPT on the draft flagged three sentences as guesswork we had not run — "TRIM leaves interior spaces alone", "a leading zero is handled by matching toward text", and "deleting a column in the middle produces #REF!". Running all three for real (04_보탬시험2.txt) led us to correct the first two. TRIM collapsed runs of interior spaces to one, and a leading zero could not be matched with TEXT(…,"0") — only TEXT(…,"0000"), with the width spelled out, worked.
• The same pass pushed us to run what happens in case ⑥ when the orders are in the same sequence as the price table, so we ran it separately (04_보탬시험2.txt). All five rows came out correct, confirming that the broken formula can stay invisible.

What we did not test

• We ran on one build only, Excel 16.0 build 20326.0. We did not check 2016 or 2019, Excel for the web, or Google Sheets.
• Which value an approximate match grabs on an unsorted table is what we observed over those eight rows; we did not look up how the internal search is documented.
• Approximate match against numeric codes (bracket lookups, such as a commission rate per amount band) was not tried. The ⑤ in this note is based on text codes.
• How this file displays in a version without XLOOKUP was not checked. If the file is going to be exchanged with other people, ask about their version first.
• INDEX/MATCH combinations, VLOOKUP against another file, and Power Query merges are out of scope this round.
• Which row is returned when the same code appears on two or more rows was not tested.
• Full-width spaces and codes with Hangul letters mixed in were not tried. The invisible characters we tested were spaces (code 32) and line breaks only.

Related notes

• Keeping the reference cell fixed when you copy a formula — the $ in absolute references
• Comparing two lists in Excel — what is in both, what is in only one
• Cleaning up an inherited Excel table — merged cells, blank rows, text dates
• When an Excel formula will not calculate

Corrected 2026-09-28: It was not explained why ID 1002 came back as 이하늘 in the leading-zero test table but 김영수 in the earlier employee sheet. Added a note that these are two different test tables.