In Excel, a VLOOKUP invalid cell reference error is usually #REF!. Check that the column number in the formula is no larger than the number of columns in its lookup range, and that the range still points to existing cells. For example, a two-column range cannot return column 3; change the column number to 2 or expand the range to include a third column.
What the VLOOKUP reference error means
#REF! means that a formula refers to a cell or range Excel cannot resolve. With VLOOKUP, the most common cause is asking for a return column that falls outside the table array. The table array is the range supplied as the second argument, and the column number is counted from the left edge of that range—not from the worksheet’s column A.
A reference can also become invalid after cells used by the formula are deleted or cut and moved. Excel often updates formula references when rows or columns are inserted or deleted, but some edits can leave a broken reference, particularly when a referenced range is removed or a formula is copied into a context where its references no longer make sense. Selecting the formula cell and reading the formula bar is a useful first step.
Not every VLOOKUP problem is a reference error. A missing match commonly produces #N/A, while an invalid column number smaller than 1 commonly produces #VALUE!. Identifying the exact displayed error helps narrow the diagnosis instead of changing the lookup range at random.
How VLOOKUP counts columns
VLOOKUP has four arguments: the value to find, the lookup table, the number of the return column within that table, and whether to use an exact or approximate match. In its basic form, the formula searches the first column of the table array and returns a value from the specified column on the same row.
For example, suppose product codes are in A2:A5, prices are in B2:B5, and the code to look up is in D2. The table array A2:B5 is two columns wide: column A is number 1 within the range, and column B is number 2. With those cells arranged as stated, enter this exact-match formula; if D2 contains P-104 and its price in B is 12.50, the result is 12.50:
=VLOOKUP(D2,$A$2:$B$5,2,FALSE)
The dollar signs make the range absolute, so it stays fixed if you copy the formula to another cell. They do not change how VLOOKUP counts columns. The final argument, FALSE, requests an exact match and is generally the appropriate choice for product codes, names, and other identifiers.
Check the column number and table width
Compare the third argument with the width of the table array. If the range is A2:B5, the third argument must be 1 or 2 to return a column from that range. A third argument of 3 requests a column that is not there, which causes #REF!. The range can be widened if the intended return values are in column C.
With product codes in A2:A5, prices in B2:B5, stock counts in C2:C5, and the requested code in D2, the formula below uses a three-column range. If D2 contains P-104 and C’s corresponding stock count is 18, it returns 18:
=VLOOKUP(D2,$A$2:$C$5,3,FALSE)
To check the width instead of counting columns manually, use Excel’s COLUMNS function. With the intended table array A2:B5, this formula returns 2, confirming that a VLOOKUP column number of 3 is out of range:
=COLUMNS($A$2:$B$5)
When editing the formula, ensure you widen the table array from its left edge without accidentally changing which column VLOOKUP searches first. VLOOKUP always looks for the lookup value in the table array’s leftmost column. Adding columns on the right can make another return column available; adding unrelated columns on the left changes the search column and may cause a different error or an unexpected result.
Repair a broken range reference
Click the cell showing the error and inspect the formula bar. If the formula visibly contains #REF! where a cell or range should appear, that reference is broken. Select the correct range directly on the worksheet while editing the formula, or type the intended address carefully. Check both the lookup range and any cell reference used as the lookup value.
- Select the formula cell and review each argument, paying particular attention to the table array and column number.
- Confirm that the lookup value is in the first column of the proposed table array.
- Select the intended table cells on the sheet, or replace the broken address with a valid range.
- Make the return-column number match the position of the desired result within that range.
- Use
FALSEfor an exact match unless you intentionally need approximate matching and have prepared the data for it.
If the formula used a range on another worksheet, verify the sheet name and that the referenced cells still exist. A cross-sheet reference normally includes the sheet name and an exclamation mark, such as Products!A2:C100. Sheet names containing spaces are enclosed in single quotation marks by Excel. If a worksheet was renamed, Excel usually adjusts formulas, but a reference to a deleted sheet or damaged range may still need to be repaired.
When you cannot tell what the original range was, look for a nearby working formula, an earlier workbook version, or the source data layout. Avoid replacing #REF! with an arbitrary range simply to make the error disappear: the formula may return a plausible but incorrect value if it is pointed at the wrong data.
Distinguish #REF! from other VLOOKUP errors
#N/A: Excel did not find a matching value, often because the lookup value is absent, has extra spaces, or is stored as text while the corresponding value is numeric. It can also occur when the lookup column is not the first column in the selected range and the formula is searching the wrong place.#VALUE!: The column index is less than 1, or another argument has an unsuitable value. Check the third argument and make sure it identifies a valid column position beginning at 1.#NAME?: Excel may not recognize a misspelled function name or text argument. Check the spelling of VLOOKUP and ensure text values in formulas are enclosed in quotation marks.- Wrong result without an error: If the last argument is omitted or set to
TRUE, VLOOKUP uses approximate matching. For reliable approximate results, the first column must be sorted in ascending order. For codes and names, an exact match is usually safer.
A frequent misconception is that a lookup value being on the “wrong side” of the return column causes #REF!. VLOOKUP’s leftmost-column rule generally causes a missing match, often #N/A, rather than a reference error. A reference error points more directly to an invalid cell reference or an impossible return-column number.
Prevent errors when copying or changing formulas
Use absolute references for a fixed lookup table when filling formulas down. For example, the dollar signs in $A$2:$B$5 lock both the columns and rows. Without them, a copied formula may shift the lookup table, which can lead to missing matches or unintended results. The reference itself may remain valid, so a shifted range does not always produce #REF!; still, it is an important source of lookup mistakes.
For a workbook that will grow, an Excel Table can make the source data easier to maintain. Select the data and use Insert > Table, then give the table and its columns clear names. Structured references can expand as rows are added, reducing the chance that new records fall outside a fixed range. This does not remove VLOOKUP’s leftmost-column restriction or make an incorrect column index valid, so review the formula after changing the table’s layout.
Before deleting a worksheet column or moving source data, check formulas that depend on it. In Microsoft 365 for Windows, Ctrl+` toggles the display of formulas on a worksheet, which can help reveal references across a sheet; press the shortcut again to return to normal view. On a Mac, the equivalent shortcut is Control+`. You can also use Formulas > Show Formulas on the ribbon. If a formula points to data on another sheet, inspect those references too.
When XLOOKUP or INDEX and MATCH may help
VLOOKUP is useful when the lookup field is at the left of the return data and the range is straightforward. Its numbered return-column argument is vulnerable to layout changes: inserting or rearranging columns can make a hard-coded index return the wrong field, even when it does not cause an error. Excel 2021 and Microsoft 365 include XLOOKUP, which lets you specify the lookup and return ranges separately and can search in either direction.
With product codes in A2:A5, prices in B2:B5, and the code to find in D2, this XLOOKUP formula returns the matching price, 12.50 for P-104 in the example data:
=XLOOKUP(D2,A2:A5,B2:B5)
XLOOKUP does not use VLOOKUP’s numeric column index, so that particular out-of-range-index problem does not apply. It still needs valid lookup and return ranges, and it may not be available in older Excel versions. If a workbook must work in older releases, INDEX and MATCH can also separate the return range from the lookup range, though that approach uses two functions and takes more setup. Switching functions is not necessary for every #REF!; correcting the existing VLOOKUP is often the quickest repair.
Frequently Asked Questions
Why does VLOOKUP return #REF! when my table has the data?
Check the third argument first. The return-column number is counted from the left edge of the table array, and it cannot exceed the array’s width. Also inspect the formula for a broken range marked #REF!.
How do I fix VLOOKUP when the column index is too large?
Change the column index to the position of the desired return field within the selected range, or expand the range to include that field. For instance, a two-column range cannot return column 3 unless you extend the range to at least three columns.
Does VLOOKUP return #REF! if the lookup value is missing?
Usually no. A missing exact match generally returns #N/A. A #REF! error more often indicates an invalid range reference or a return-column number beyond the table array.
Why does my VLOOKUP work in one row but not after I copy it?
The lookup range may have shifted because it was entered as a relative reference. Lock a fixed range with dollar signs, such as $A$2:$B$100, and check that the lookup-value reference changes as intended when the formula is filled down.
Can I use VLOOKUP to return a value to the left of the lookup column?
No. VLOOKUP searches the first column of its table array and returns a value from a column to its right. Reorder the data or use XLOOKUP in Excel 2021 or Microsoft 365, or use another lookup method that supports separate lookup and return ranges.
Related
- Excel Shortcut to Insert Another Row in Windows and Mac
- Excel Shortcut to Select to the End of a Column
- Excel Shortcut to Select Cells to the End of a Column
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- SUMPRODUCT Formula Not Working? Fix the Common CausesIf SUMPRODUCT is not working in Excel, first check that every range in the formula has the same…
- COUNTIFS Not Counting Correctly? Fix the CriteriaIf COUNTIFS is returning zero or the wrong total in Excel, first check that every criteria range has…
- Excel Autofit Row Height Adding Extra Line? FixYes, Excel’s autofit row height can add an extra line of space, and the most common cause is…
- Excel Too Many Arguments Error: Causes, Fixes, and ExamplesExcel’s “You’ve entered too many arguments for this function” message means a formula supplies more inputs than that…
- Bing Chat Excel Formula Not Working? Fixes That HelpIf a formula from Bing Chat is not working in Excel, check that it begins with an equals…
- Excel Filter Not Working in SharePoint? Causes and FixesIf a filter is not working in Excel in SharePoint, first check that the filter covers the full…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.