VLOOKUP 返回错误值？近似匹配陷阱与其他 4 个原因

Tested in: Live formula checks in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026; Google Sheets (spreadsheet locale zh_CN), November 9, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow.

Which versions this applies to

Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016, and Google Sheets. The formulas work in both unless noted. If your region uses commas as decimal separators, replace the commas between arguments with semicolons.

Example data to reproduce the problem

Put this price list in A1:C5 and the lookup in F1:G2:

ID 1005 doesn’t exist, but G2 returns 7.25, the price of the Stapler (1004). No error, just a wrong answer. That’s the approximate match trap.

Cause 1: The approximate match trap (missing or TRUE fourth argument)

What happens: Microsoft’s documentation says that if range_lookup is TRUE or left out, VLOOKUP assumes the first column is sorted and searches for the closest value. Google’s documentation says the same for is_sorted: TRUE is the default, and an approximate match returns the closest value that is smaller than your search key. In the example, 1004 is the largest ID that’s smaller than 1005, so you get the Stapler’s price.

It gets worse when the first column isn’t sorted. Both Microsoft and Google warn that you’ll likely get an unexpected value. The result depends on how the search lands in the unsorted list, so it can look random.

How to detect:

• The formula has only three arguments, e.g. =VLOOKUP(F2, A2:C5, 3), or the fourth argument is TRUE or 1.
• Test with an ID you know doesn’t exist. With approximate match you get a value; with exact match you get #N/A.

How to fix: add FALSE (or 0):

=VLOOKUP(F2, $A$2:$C$100, 3, FALSE)

Google’s VLOOKUP help explicitly recommends FALSE and recommends always typing the fourth argument for readability. If you’d rather show a message than #N/A for missing IDs:

=IFNA(VLOOKUP(F2, $A$2:$C$100, 3, FALSE), "Not found")

When approximate match is correct: tiered lookups such as commission rates, tax brackets or grade boundaries. Sort the first column in ascending order and keep TRUE on purpose:

=VLOOKUP(B2, $H$2:$I$4, 2, TRUE)

A sales value of 7,200 returns 3%, because 5,000 is the largest threshold that’s at or below 7,200.

One more trap that shows up with approximate match: numbers stored as text in the first column. Microsoft’s best-practice list warns that this can make VLOOKUP return an incorrect or unexpected value. See Convert Numbers Stored as Text in Excel and Google Sheets (5 Methods).

Cause 2: Duplicate keys (VLOOKUP returns the first match only)

What happens: Google’s documentation states that if several rows match, VLOOKUP returns the value for the first match in the search column. Excel behaves the same way. If your order list has the same customer ID on several rows, you’ll always get the oldest (top) row, which may not be the one you want.

How to detect: count how often the lookup value appears:

=COUNTIF($A$2:$A$100, F2)

Anything above 1 means VLOOKUP is picking the top row of several.

How to fix:

• If the duplicates are mistakes, remove them (Excel: Data > Remove Duplicates; Sheets: Data > Data cleanup > Remove duplicates).
• If you want the last (most recent) match, use XLOOKUP with search_mode set to -1, which searches from the last row up. This works in Excel 365, Excel 2021/2024 and Google Sheets:

=XLOOKUP(F2, $A$2:$A$100, $C$2:$C$100, "Not found", 0, -1)

• In Excel 2016 or 2019 (no XLOOKUP, per Microsoft), this classic pattern returns the last match:

=LOOKUP(2, 1/($A$2:$A$100=F2), $C$2:$C$100)

• If you need all matches, use =FILTER($C$2:$C$100, $A$2:$A$100=F2) (Excel 365/2021+ and Google Sheets).

Cause 3: A hard-coded column number that now points to the wrong column

What happens: col_index_num is just a number. If someone inserts a column inside your table, the range expands, but the 3 in your formula doesn’t change. VLOOKUP now returns whatever moved into the third column. You get a wrong value with no error.

How to detect: count the columns of your range from the left, starting at 1 (Google’s docs stress counting from the selected range, not the whole sheet). Does column number N actually contain what you want?

How to fix: let MATCH find the column by its header, so inserted columns don’t matter:

=VLOOKUP(F2, $A$1:$D$100, MATCH("Price", $A$1:$D$1, 0), FALSE)

Or skip the column number entirely with XLOOKUP (Excel 365/2021+, Google Sheets):

=XLOOKUP(F2, $A$2:$A$100, $C$2:$C$100, "Not found")

Or with INDEX/MATCH, which works in every Excel version:

=INDEX($C$2:$C$100, MATCH(F2, $A$2:$A$100, 0))

Cause 4: The lookup range moved when you copied the formula

What happens: without dollar signs, A2:C5 becomes A3:C6, then A4:C7 as you fill the formula down. Each row searches a smaller, shifted slice of the table. With exact match, the rows that fall out of range return #N/A. With approximate match (Cause 1), they often return a wrong value instead, which is harder to spot.

How to detect: click a formula a few rows down and check the range in the formula bar. If it no longer starts at your first data row, this is the cause.

How to fix: lock the range with absolute references (press F4 in Excel, or F4/Fn+F4 in Sheets after selecting the reference). Both Microsoft and Google list this as a best practice:

=VLOOKUP(F2, $A$2:$C$100, 3, FALSE)

Keep F2 relative so it moves down row by row. In Excel, you can also convert the data to a table (Insert > Table) and use structured references, which don’t shift.

Cause 5: Wildcard characters in the lookup value

What happens: with exact match, VLOOKUP treats * (any characters) and ? (any single character) as wildcards when the lookup value is text. Both Microsoft and Google document this. If your product codes contain these characters, the lookup can match a different row.

=VLOOKUP("BOLT*", A2:B3, 2, FALSE) returns 0.40, because BOLT* means “BOLT followed by anything”, and BOLT-10 is the first row that fits.

How to detect: check whether your lookup values contain *, ? or ~:

=SUMPRODUCT(--ISNUMBER(FIND({"*","?","~"}, F2)))>0

TRUE means the value contains at least one of those characters. (FIND is used instead of SEARCH because FIND never treats * or ? as wildcards.)

How to fix in Excel: escape the characters with a tilde before looking them up. Microsoft documents the tilde escape for VLOOKUP; Google documents it for COUNTIF-style criteria.

=VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2,"~","~~"),"*","~*"),"?","~?"), $A$2:$B$100, 2, FALSE)

The simpler option is XLOOKUP. Its default match_mode of 0 is a plain exact match; wildcards only apply when you set match_mode to 2:

=XLOOKUP(F2, $A$2:$A$100, $B$2:$B$100, "Not found")

Bonus check: VLOOKUP ignores case

VLOOKUP treats “abc-1” and “ABC-1” as the same key, so a case-sensitive code list can return the wrong row. For a case-sensitive lookup in Excel 365/2021+:

=INDEX($B$2:$B$100, MATCH(TRUE, EXACT($A$2:$A$100, F2), 0))

In Excel 2019 or older, confirm it with Ctrl+Shift+Enter. In Google Sheets, wrap it in ARRAYFORMULA:

=ARRAYFORMULA(INDEX($B$2:$B$100, MATCH(TRUE, EXACT($A$2:$A$100, F2), 0)))

Excel vs. Google Sheets

The trap is identical in both apps, so a formula that’s wrong in one is wrong in the other.

Common mistakes

1. Assuming “no error” means “correct”. Approximate match never says it guessed. Spot-check with an ID that doesn’t exist.
2. Sorting the data to “fix” it, then keeping TRUE. Missing IDs still return a neighbor’s value. Use FALSE for ID lookups.
3. Typing "FALSE" in quotes. Use the logical FALSE or 0, not text.
4. Counting columns from column A of the sheet instead of from the first column of the range.
5. Ignoring stray spaces. "Apple " and "Apple" are different keys; this usually causes #N/A, covered in XLOOKUP Returns #N/A Even Though the Value Exists: 7 Fixes.

FAQ

Why does VLOOKUP return a value for an ID that doesn’t exist?

The fourth argument is missing or TRUE, so VLOOKUP does an approximate match and returns the closest smaller key’s row. Add FALSE as the fourth argument to get #N/A for missing IDs instead.

Is VLOOKUP’s default TRUE or FALSE?

TRUE (approximate match) in both Excel and Google Sheets. That’s why you should always type FALSE for exact lookups.

Why does VLOOKUP return the first match instead of the latest one?

VLOOKUP always returns the first matching row from the top. To get the last match, use XLOOKUP with search_mode -1, for example =XLOOKUP(F2, A2:A100, C2:C100, “Not found”, 0, -1).

Why did my VLOOKUP results change after I inserted a column?

The column number in the formula is fixed, so after an insert it points to a different column. Use MATCH on the header row for the column number, or switch to XLOOKUP or INDEX/MATCH.

Is VLOOKUP case-sensitive?

No. To match case exactly, use INDEX with MATCH(TRUE, EXACT(…), 0), wrapped in ARRAYFORMULA in Google Sheets.

Related tutorials

• XLOOKUP Returns #N/A Even Though the Value Exists: 7 Fixes
• COUNTIF vs COUNTIFS vs SUMPRODUCT: When to Use Each (With Examples)
• TRIM Not Working in Excel? Remove Non-Breaking Spaces (CHAR 160)

Live-test findings

Sheets wildcard difference: the three-SUBSTITUTE chain successfully looked up literal BOLT* (0.55) and ? characters. However, doubling a literal tilde changed A~B to A~~B and returned #N/A. Looking up bare A~B returned 40. Do not apply the full Excel escape chain indiscriminately in Sheets; keep literal tildes unchanged for this case.

The approximate-match trap was also reproduced in Excel for Mac: omitting FALSE for 1005 returned 7.25, while exact match returned #N/A.

Sources

• Microsoft Support: VLOOKUP function (range_lookup default, sorting, wrong value, wildcards and tilde, numbers stored as text, absolute references): https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
• Microsoft Support: XLOOKUP function (match_mode, search_mode -1, not available in Excel 2016/2019): https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
• Google Docs Editors Help: VLOOKUP (is_sorted default TRUE, first match returned, wildcards, column index, absolute references): https://support.google.com/docs/answer/3093318
• Google Docs Editors Help: XLOOKUP function (match_mode, search_mode): https://support.google.com/docs/answer/12405947
• Google Docs Editors Help: COUNTIF (tilde escape for wildcards): https://support.google.com/docs/answer/3093480