VLOOKUP Not Working? Fix Common Excel Lookup Errors

If VLOOKUP is not working, first check that the lookup value is in the leftmost column of the selected table and add FALSE as the last argument for an exact match. Then check for mismatched data types, extra spaces, an incorrect column number, or a missing match; these are among the most common causes of errors and unexpected results.

What VLOOKUP is supposed to do

VLOOKUP searches down the first column of a table for a value, then returns a value from another column on the same row. Its syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The first three arguments are required: the value to find, the range containing the data, and the number of the return column counted from the left edge of that range. The optional final argument controls whether Excel looks for an exact or approximate match.

For example, suppose A2:B5 contains product codes in column A and product names in column B, and D2 contains the code you want to find. The following formula searches the first column of A2:B5 and returns the matching name from its second column. If D2 contains 103 and that row has the name Cedar, the result is Cedar.

With product codes in A2:A5, names in B2:B5, and the code to look up in D2, use:

=VLOOKUP(D2,A2:B5,2,FALSE)

VLOOKUP only searches the first column of the selected range, and it can return values only from columns to the right of that column. Those design limits explain many formulas that appear reasonable but cannot return the desired result.

Check the formula arguments first

A small mistake in any argument can make the formula fail or return the wrong value. Verify the arguments from left to right before changing the source data:

Lookup value: Confirm that the formula refers to the cell containing the value you intend to search for. A reference to a blank or neighboring cell may produce an error or an unexpected match.
Table array: Make sure the range includes both the lookup column and the column with the answer. The lookup column must be the range’s leftmost column.
Column index: Count columns from the start of the selected range, not from the worksheet. For A2:C20, column A is 1, B is 2, and C is 3.
Match mode: Use FALSE when you need an exact match. Do not leave this argument out unless approximate matching is intentional.

For instance, if the table range is B2:D20 and the answer is in worksheet column D, the column index is 3, not 4: the selected range begins in B. If the index is larger than the number of columns in the range, Excel returns #REF!.

Use exact match unless you need an approximate match

The fourth argument is a frequent source of trouble. With FALSE (or 0), VLOOKUP searches for an exact match. With TRUE (or 1), it performs an approximate match. If you omit the argument, Excel uses approximate matching by default, which can surprise someone who expects an exact search.

Approximate matching is useful for ordered thresholds, such as assigning a rate based on a score band. In that case, the first column of the lookup table must be sorted in ascending order for results to be reliable. Excel returns the largest first-column value that is less than or equal to the lookup value. If the lookup value is below the smallest threshold, the formula can return #N/A.

For customer IDs, invoice numbers, names, or other values that should match one specific entry, choose exact match. For a score-band table, choose approximate match only when the thresholds are deliberately set up and sorted for that purpose. A formula that returns a plausible but incorrect result may be using approximate matching against an unsorted list.

Make sure the lookup column is in the right place

VLOOKUP cannot look left. If you select a table beginning in column B, it searches column B and can return a result from C, D, or another column to its right; it cannot search column C and return a value from B. Selecting a range that starts one column too far left or right can therefore change what VLOOKUP searches.

To fix this, adjust the table range so the lookup values are its first column, or rearrange a copy of the data if that is practical. In Microsoft 365, XLOOKUP is often easier when the return column is to the left, because it accepts separate lookup and return ranges. The alternative section below covers that option.

Also check whether the formula’s table range shifts as you copy it. If the table is in A2:B100, an ordinary reference may move down when the formula is filled to other rows. Lock the range with dollar signs when it should stay fixed. With product codes in A2:A5, names in B2:B5, and each lookup code in D2, this formula keeps the table range fixed when copied down; for D2 containing 103, it returns Cedar.

=VLOOKUP(D2,$A$2:$B$5,2,FALSE)

Look for spaces and mismatched data types

Two entries can look identical on screen but differ internally. A number stored as text, such as the text value “103,” may not match the numeric value 103. Leading zeros can matter too: a code stored as text “00103” is not the same identifier as numeric 103, which does not retain those leading zeros. Decide whether the key is meant to be a number or a text code, then make the lookup value and source keys use the same representation.

Extra spaces are another common cause of #N/A. A trailing space after a name, a leading space before a code, or a nonbreaking space copied from a website can prevent an exact match. Inspect the source and lookup value, and consider using Excel’s Find and Replace or a helper column to clean the keys. Ordinary TRIM removes excess regular spaces, but it does not remove every kind of invisible character.

For example, with a potentially messy value in A2, this helper formula replaces nonbreaking spaces with ordinary spaces, removes nonprinting characters recognized by CLEAN, and trims excess regular spaces. If A2 contains the text “ Cedar ” with surrounding regular spaces, the result is Cedar. Apply equivalent cleanup to both the lookup value and the table’s key column; cleaning only one side can leave them different.

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

When converting keys, avoid changing identifiers casually. Converting “00103” to a number loses its leading zeros, which may be part of the actual code. Keep identifiers as text if their formatting is meaningful, and make both sides consistent instead of forcing a conversion that changes the key.

Understand the error or wrong result

The error message often points toward the cause, though it does not identify it by itself:

#N/A: An exact match was not found. Check spelling, spaces, data types, the selected lookup column, and whether the value exists in the source range. With approximate matching, check the sort order and whether the value is below the smallest entry.
#REF!: The column index asks for a column outside the selected table range, or a referenced column has been deleted. Count the columns in the current range again.
#VALUE!: The column index may be less than 1, or an argument may not be in the expected form. Check the formula’s commas, parentheses, and index number.
#NAME?: Excel may not recognize a misspelled function name, or text in the formula may not be enclosed in quotation marks where required.

If the formula returns a value but it is the wrong one, confirm that the match mode is exact when appropriate, the range starts at the intended lookup column, and the column index points to the intended answer. Also check duplicate keys: VLOOKUP returns the first matching row it encounters, not every matching row. If the data contains duplicates, verify that the first occurrence is the one you want.

Work through a reliable troubleshooting sequence

Rather than rewriting the formula at random, isolate the problem in a consistent order. This makes it easier to distinguish a formula-reference issue from a problem in the underlying data.

Click the formula cell and confirm the formula begins with =VLOOKUP and has four arguments, including FALSE for an exact match.
Check that the lookup value appears in the first column of the selected range. Search that column directly, and inspect the source cell rather than relying only on how it looks.
Count the table’s columns from left to right and verify that the column index identifies the desired return value.
Compare the lookup value and source key for data type, leading zeros, spaces, and hidden characters. Clean or standardize both sides if necessary.
Check for duplicate keys and confirm that the first match is the desired record.
If the formula was copied, inspect its references. Confirm that the lookup-cell reference changes as intended and the table range stays fixed if it should.

For diagnosis, use a simple formula in an empty cell before adding extra logic such as IFERROR. Hiding an error can make a workbook look cleaner, but it does not correct a missing key or an incorrect range. Once the cause is fixed, an error-handling wrapper may be useful if a blank or a custom message is more appropriate than showing #N/A.

Consider XLOOKUP or another alternative

In Microsoft 365, XLOOKUP can make some lookups clearer because the lookup range and return range are specified separately. It can search in either direction, uses exact matching by default, and lets you provide a value to show when no match is found. XLOOKUP is also available in Excel 2021; it is not available in some older Excel versions.

With product codes in A2:A5, names in B2:B5, and the code in D2, this formula returns Cedar when D2 contains 103, or displays Not found when there is no exact match:

=XLOOKUP(D2,A2:A5,B2:B5,"Not found")

Another option is INDEX with MATCH, which is useful in older Excel versions and can return a value from a column on either side of the search column. The best choice depends on the workbook’s version and needs: VLOOKUP remains familiar and works in older files, while XLOOKUP removes the left-to-right restriction and avoids counting a return-column index.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is there?
Often the entries only appear identical. Compare text and number types, spaces, hidden characters, and leading zeros. Also confirm that the value is in the first column of the selected range and use exact matching when you need an exact result.

Why does VLOOKUP return the wrong row?
Check whether the last argument is omitted or set to TRUE, which requests approximate matching. For an exact lookup, use FALSE. If the result is still wrong, look for duplicate keys; VLOOKUP returns the first match.

Does VLOOKUP search from right to left?
No. It searches the leftmost column of its table range and returns data from a column to the right. Use XLOOKUP in a supported Excel version, or another approach such as INDEX with MATCH, when the return column is on the left.

Why does my VLOOKUP formula change when I copy it?
Relative references move when a formula is copied. If the lookup table should remain in the same place, use absolute references by adding dollar signs to its row and column references. Keep the lookup-cell reference relative if it should update for each row.

Should I use TRUE or FALSE in VLOOKUP?
Use FALSE for an exact match, which is the usual choice for IDs, names, and individual records. Use TRUE only for intentional approximate lookups, with the first column sorted in ascending order.

Why does Excel reject commas in my formula?
Some regional settings use semicolons rather than commas as argument separators. If Excel reports a formula syntax problem, try the separator used by your installation. The function name and argument order remain the same.

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.