VLOOKUP Table Array Not Working? Fix the Range

If your VLOOKUP table array is not working, check that the lookup value is in the first column of the selected range, the range includes the return column, and the match setting is correct. In Excel for Microsoft 365 on Windows, a dependable starting point is to use an absolute table range and set the last argument to FALSE for an exact match.

What the table array means

VLOOKUP searches for a value in the leftmost column of a rectangular range, then returns a value from another column in the same row. That rectangular range is the table array. It can be a worksheet range, a named range, or an Excel table. VLOOKUP does not search every column for the key: the key must be in the first column of whichever array you provide.

For example, suppose A2:A100 contains product codes, B2:B100 contains product names, and C2:C100 contains prices. You want the price for the code in E2. With product codes in A2:A100 and prices in the third column of A2:C100, use:

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

The range starts at column A, so its first, second, and third columns are A, B, and C. The number 3 means “return the third column of this selected range,” not “return worksheet column C” in all circumstances. FALSE requests an exact match. If the code is present once, Excel returns the price from its row; if it is absent, the result is usually #N/A.

Check the array boundaries and column number

A frequent mistake is selecting a range that begins too far to the right or ends too soon. If the code is in column A but the array starts at B, VLOOKUP cannot use A as its search column. If the array ends at B while the formula asks for column 3, the requested return column is outside the array and Excel returns #REF!.

Count the return column from the left edge of the array, not from the worksheet’s column letters. For an array of B2:D100, B is column 1, C is column 2, and D is column 3. This distinction matters when the search column is not in worksheet column A.

Confirm the first column of the selected range contains the lookup key.
Confirm the range reaches the column whose value you want returned.
Count the return column from the array’s left edge and use a positive whole number.
Check that the lookup value and return value are on the same row in the source data.

When copying a formula down a list, the array can shift unintentionally. In the example above, the dollar signs in $A$2:$C$100 lock the range so it stays fixed as the formula is copied. In Windows Excel, select the range reference in the formula bar and press F4 to cycle through reference types. On a Mac, the shortcut can depend on the keyboard and system settings; you can also type the dollar signs directly.

Use the right match setting

VLOOKUP’s fourth argument controls how it matches. FALSE means an exact match; TRUE means an approximate match. If you omit the argument, VLOOKUP uses approximate matching. That default is a common reason a formula returns a plausible but incorrect result rather than an error.

For product codes, names, employee IDs, and most ordinary lookups, use FALSE. With FALSE, the key must match a value in the first array column. If the list contains duplicate keys, VLOOKUP returns the first matching row it encounters, so check for duplicates when the returned value seems wrong.

Approximate matching has a narrower use, such as assigning a rate based on a threshold. In that case, the first column should be sorted in ascending order, and the result is for the largest value that is less than or equal to the lookup value. An unsorted list can produce an unexpected answer. Do not rely on approximate matching for an unsorted list of labels or identifiers.

Look for mismatched data types and hidden characters

A value that looks identical on screen may not be stored identically. For example, a number 27 and text “27” can fail to match in an exact lookup. This often happens when one column was imported from a text file or copied from a website. A green error indicator or the Number Format settings may help identify numbers stored as text, but changing the display format alone does not always convert the underlying value.

Compare the source key and lookup value carefully. Check whether one has leading or trailing spaces, a nonbreaking space copied from a webpage, or punctuation that is difficult to see. Codes with leading zeros deserve particular care: “0074” may be an identifier, not the number 74. Converting such codes to numbers removes the zeros and can make matching fail or change the key’s meaning.

Check a few source values and the lookup value in the formula bar, not only their displayed appearance.
For numeric quantities, make sure both sides are genuinely numbers or both are text.
For IDs with leading zeros, preserve them as text consistently in both places.
Remove unwanted spaces or other imported characters from the data, then test the lookup again.

TRIM can remove ordinary leading and trailing spaces from text, but it does not remove every possible invisible character. If cleaning the data, use a helper column and compare the cleaned values before replacing original records. This is safer than assuming a formatting change will repair the underlying contents.

Diagnose the error or unexpected result

The displayed result is a useful clue. #N/A generally means Excel did not find an exact match, or that the search column is not the one you intended. #REF! commonly means the column index asks for a column beyond the array. #VALUE! can indicate an invalid argument, including a column index below 1. A wrong but non-error result often points to approximate matching, duplicate keys, or a column number counted from the worksheet rather than the array.

Use a short, orderly check rather than rewriting the formula at random:

Click the cell and inspect the formula’s lookup value, array, column index, and match argument.
Verify that the lookup key appears in the array’s first column and is stored in a compatible format.
Check the array’s starting and ending rows and columns; make sure it includes the relevant record and return field.
Set the last argument to FALSE if you need an exact lookup.
Check the source for duplicate keys if the answer is valid-looking but belongs to the wrong record.

Excel’s Formulas > Evaluate Formula command on Windows can step through a formula and show how its parts are evaluated. It is particularly useful when a reference has been copied, edited, or built from several cell references. On Mac, ribbon availability and layout can vary by Excel build, but the formula can also be inspected directly in the formula bar.

Make the reference easier to maintain

A fixed cell range is simple, but it will not necessarily expand when new records are added below it. If the source list grows, either update the range or convert the data to an Excel table using Insert > Table. Tables expand as data is added and make source data easier to manage. Give the table a clear name under Table Design, then use that name as the array.

For example, assume an Excel table named Inventory has three columns in this order: SKU, Description, and UnitPrice. If the SKU to find is in E2 and UnitPrice is the third table column, use:

=VLOOKUP(E2,Inventory,3,FALSE)

This assumes the table’s first column is SKU and its third column is UnitPrice. If you rearrange the table’s columns, the number 3 still means the third column in the table, so verify the layout after structural changes. A table name also avoids a hard-coded last row, but it does not remove VLOOKUP’s leftmost-column requirement.

Named ranges can make formulas more readable too, provided the name refers to the intended rectangular range. In Formulas > Name Manager, inspect the name’s “Refers to” definition if a named array unexpectedly omits rows or columns. A correct-looking formula can still fail if its name points to an old or incomplete range.

When to use an alternative

VLOOKUP is useful when the lookup key is the leftmost column and the return field is to its right. If the return field is left of the key, VLOOKUP cannot return it by changing the column number; the array must still have the lookup column first. You could rearrange the data, or use a function that separates the lookup and return ranges.

In Microsoft 365 or Excel 2021, XLOOKUP can search one range and return a result from another, including a column to the left. With product codes in A2:A100, prices in C2:C100, and the requested code in E2, use:

=XLOOKUP(E2,A2:A100,C2:C100,"Not found")

This returns the matching price, or “Not found” if there is no match. XLOOKUP uses exact match by default, though it has options for other match behaviors. It is not available in every older Excel release, so check the version if a workbook will be shared with people using earlier software. VLOOKUP remains a practical choice when compatibility and a leftmost lookup column matter.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is visible?
It may be stored as text on one side and a number on the other, contain an invisible space, or differ in punctuation. Also confirm that the visible value is in the first column of the selected array and that the formula uses the intended lookup cell.

Does the table array have to start in column A?
No. It can start in any worksheet column. Its first column simply has to be the column VLOOKUP searches. The column index is counted from the array’s left edge, not from column A of the worksheet.

Why does VLOOKUP return the wrong row without an error?
Check whether the fourth argument is omitted or TRUE, which requests approximate matching, and whether the first array column is sorted. If using FALSE, check for duplicate keys: VLOOKUP returns the first match.

How do I stop the table array changing when I copy the formula?
Use absolute references, such as $A$2:$C$100, for a worksheet range. The dollar signs keep the selected rows and columns fixed when the formula is filled into other cells.

Can VLOOKUP return a value from a column to the left?
No. The lookup key must be in the array’s first column, and VLOOKUP returns from a column to its right. Rearrange the data or use XLOOKUP in Microsoft 365 or Excel 2021 when available.

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.