VLOOKUP Exact Match Not Working? Fix the Common Causes

If VLOOKUP exact match is not working, first make sure the formula ends with FALSE (or 0), then check that the lookup value and the first column of the search range contain the same kind of value, with no extra spaces or hidden characters. The most common symptom is #N/A, but a wrong range or column number can return the wrong result or a different error.

What exact match means in VLOOKUP

VLOOKUP uses four arguments: the value to find, the table range, the number of the return column within that range, and whether to use an approximate or exact match. Set the fourth argument to FALSE to ask Excel to find an exact match. 0 has the same effect. With this setting, the lookup column does not need to be sorted.

Leaving out the fourth argument is a frequent cause of confusion. Excel then uses approximate matching, which expects the first column to be sorted in ascending order and can return a nearby value instead of the one you intended. For dependable exact lookups, include FALSE explicitly, even if a formula currently appears to work without it.

“Exact” refers to matching the lookup value against a cell in the first column of the selected range; it does not mean that Excel checks every part of the workbook or searches all columns. VLOOKUP also does not distinguish uppercase from lowercase letters. One exception to be aware of: the wildcard characters * and ? can act as wildcards in a text lookup even when the match setting is FALSE. To search for a literal wildcard character, put a tilde before it, such as ~* for a literal asterisk.

Check the formula and the lookup range first

Start with a small, known example and confirm each argument. Assume IDs are in A2:A4, the corresponding regions are in B2:B4, and the ID to find is in D2. With D2 containing 1042 and A2:B4 containing 1042 / North, 1043 / South, and 1044 / West, this formula returns North:

With IDs in A2:A4, regions in B2:B4, and the lookup ID in D2, use =VLOOKUP(D2,$A$2:$B$4,2,FALSE). For D2 equal to 1042, the result is North.

Check that the range starts with the lookup column. VLOOKUP only searches the leftmost column of its table range, so a range such as B2:A4 will not make column A searchable as its first column. Also count the return-column number from the left edge of the selected range, not from the worksheet. In A2:B4, column B is number 2; in C2:E4, column E is number 3.

Confirm the formula’s fourth argument is FALSE or 0.
Make sure the lookup column is the first column in the range.
Check that the return-column number is within the range and points to the intended field.
Use dollar signs to lock a range if you will copy the formula down; otherwise the range may shift.

Check for numbers stored as text

Two cells can look identical on screen but hold different types of values. For example, one ID may be the number 1042 and the other the text string “1042.” This often happens when data is imported from a CSV, copied from a website, or entered with a leading apostrophe. An exact lookup may fail because the stored values do not match as expected.

Check the cell format and the way the values behave. A green triangle may indicate a number stored as text, though its absence does not prove that the value is numeric. You can test the lookup cell directly. With the lookup value in D2 stored as text, this check returns FALSE; if D2 contains a numeric value, it returns TRUE:

With the lookup value in D2, use =ISNUMBER(D2). If D2 contains the text string “1042,” the result is FALSE.

Make both sides consistent rather than converting values indiscriminately. If the values are quantities, converting text numbers to numbers can be appropriate. If they are identifiers, leading zeros may matter: the text ID “001042” is not interchangeable with the number 1042. In that case, keep both lookup columns as text and preserve the same character sequence.

For a simple text number with ordinary spaces around it, a helper column can convert it to a number. With the imported value in A2 containing the text “ 1042 ”, use =VALUE(TRIM(A2)) in a helper column; it returns the number 1042. Fill the helper formula down, and use the cleaned helper column as the lookup column. Do not use this approach for codes whose leading zeros are meaningful.

Remove extra spaces and hidden characters

Leading or trailing spaces are another common cause of #N/A. A cell containing “North” does not match a cell containing “North ”, even though the difference is easy to miss. Nonbreaking spaces and other imported control characters can be harder to spot. Excel’s TRIM removes ordinary leading and trailing spaces and reduces repeated spaces between words; CLEAN removes many nonprinting control characters. Neither solves every character-import problem by itself.

For a value in A2 that may include a nonbreaking space, use a helper formula to replace that character with an ordinary space before trimming and cleaning. With the troublesome text in A2, =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) returns North when A2 contains “ North ” with a nonbreaking space at an edge. Apply equivalent cleanup to the lookup value and the lookup column; cleaning only one side can leave the mismatch in place.

Keep the original data until you have verified the cleaned results. A helper column makes it possible to compare the source and normalized values before changing or replacing anything. If the values are supposed to be fixed identifiers, consider copying the validated helper results and using Paste Special > Values to store them as plain values.

Look for a shifting or incorrect table range

A formula can work in one row and fail after being filled down if its table range moves. For example, a relative reference to A2:B100 becomes A3:B101 in the next row. Locking the range as $A$2:$B$100 keeps it fixed while the lookup cell changes. In Excel for Microsoft 365 on Windows, select a reference while editing the formula and press F4 to cycle through reference styles. On Mac, the equivalent shortcut is commonly Command+T, though keyboard settings can affect function-key behavior.

Also check that the lookup table includes every row of source data. If new records were added below the range, VLOOKUP cannot find them outside the selected area. An Excel table can expand as rows are added: select the data and use Insert > Table, then confirm the header setting. You can also deliberately select a larger range, but avoid including unrelated columns that make the return-column index hard to audit.

A valid match can still return the wrong field if the column index is off by one. For example, if the selected range is A2:C100, an index of 2 returns values from column B, while 3 returns values from C. If you insert or rearrange columns, revisit the formula’s index rather than assuming it still points to the same field.

Distinguish #N/A from other errors

#N/A usually means VLOOKUP did not find a matching value in the first column, but it does not identify the cause. Check the data type, spaces, hidden characters, spelling, and whether the value is actually inside the selected range. A filter or hidden row does not normally prevent VLOOKUP from searching that row.

#REF! commonly means the return-column index is larger than the number of columns in the selected range, or that a referenced cell or range has been deleted. #VALUE! can indicate an invalid argument, including a column index below 1. If the formula returns a plausible but incorrect result, check especially for an omitted fourth argument, an approximate match, a shifted range, or a wrong return-column index.

If you want a friendly message instead of #N/A, wrap the lookup in IFNA. With IDs in A2:A4, regions in B2:B4, and the requested ID in D2, =IFNA(VLOOKUP(D2,$A$2:$B$4,2,FALSE),"Not found") returns North for 1042 and Not found for an ID absent from A2:A4. This changes how a missing match is displayed; it does not repair the underlying lookup problem, so use it after checking the data.

A practical troubleshooting sequence

Work from the formula outward so you can isolate the cause instead of making several changes at once. In Microsoft 365 for Windows, click the formula cell and inspect the formula bar. You can also use Home > Find & Select > Find to locate a value in the sheet. Ctrl+F opens Find on Windows; Command+F is the usual equivalent on Mac. Excel for the web offers the same core VLOOKUP exact-match behavior, though ribbon layout and keyboard shortcuts can vary by browser and platform.

Test one known ID and explicitly set the fourth argument to FALSE.
Verify the lookup value exists in the range’s first column and that the range covers all source rows.
Compare the two cells’ stored types and check for spaces, nonbreaking spaces, or characters that are not visible.
Confirm the return-column index and lock the range before filling the formula down.
Use a helper column to normalize data, then rerun the lookup against the normalized values.

Changing the cell’s number format alone usually does not convert text into a number; it changes how a value is displayed. If you use Data > Text to Columns to convert imported values, review the preview and conversion settings first, especially for IDs, dates, and values with leading zeros. A helper formula is often easier to reverse and verify.

When XLOOKUP or another method may be better

VLOOKUP remains useful for a straightforward lookup where the key is in the leftmost column and the return column is to its right. If the key is elsewhere, you can rearrange the source or use another lookup approach. XLOOKUP can search in either direction and lets you specify exact matching directly; it is available in Microsoft 365 and Excel 2021 or later. For example, with IDs in A2:A4, regions in B2:B4, and the requested ID in D2, =XLOOKUP(D2,A2:A4,B2:B4,"Not found",0) returns North for 1042 and Not found when no exact ID is present.

Switching functions does not automatically fix inconsistent data. XLOOKUP cannot make a text ID equal to a numeric ID or remove a hidden space unless you normalize the data. If a workbook must be opened in an older Excel version, check function compatibility before replacing VLOOKUP with XLOOKUP; newer functions may not be available there.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is visibly there?
The displayed values may differ in type or contain extra spaces, nonbreaking spaces, or other hidden characters. Also verify that the matching value is in the first column of the selected range and inside the range boundaries.

Does VLOOKUP exact match require the first column to be sorted?
No. With the fourth argument set to FALSE or 0, the first column does not need to be sorted. Sorting is important for approximate matching, which is the default if the fourth argument is omitted.

Are FALSE and 0 the same in a VLOOKUP formula?
Yes. Both specify an exact match. Writing FALSE can make the formula’s intent easier for a person to recognize.

Can VLOOKUP match numbers stored as text?
Do not assume that a numeric value and a text representation of that number will match. Make the lookup value and lookup column consistent, while preserving text formatting for IDs where leading zeros matter.

Why does VLOOKUP return the wrong column?
The third argument counts columns from the left edge of the table range. Recheck that index after changing the range or rearranging source columns; the lookup column itself is counted as column 1.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.