In Excel for Microsoft 365 on Windows, VLOOKUP most commonly returns #VALUE! when its third argument, col_index_num, is less than 1 or is not a valid column number. Check that argument first; it must be a positive number identifying a column in the lookup range. If it is valid, inspect the other arguments and any formulas or cells they depend on, because an error in the lookup value or source data can also be passed through.
What VLOOKUP needs to work
VLOOKUP searches for a value in the first column of a range and returns a value from another column in the same row. Its arguments are the value to find, the lookup range, the number of the return column within that range, and an optional match setting. The column number is counted from the left edge of the selected range, not from the worksheet’s column letters.
For example, if a lookup range starts in column A and extends through column C, column 1 means A, column 2 means B, and column 3 means C. If the range instead starts in column D and runs through F, column 1 means D. This relative counting is an easy detail to overlook when editing a formula or copying it to another location.
With the lookup value in E2, a product table in A2:C100, and the price in the third column of that table, this formula searches for an exact match:
=VLOOKUP(E2,A2:C100,3,FALSE)
The formula’s third argument is 3 because the desired price is in the third column of A2:C100. The final argument, FALSE, requests an exact match. If E2 matches a product code in column A, Excel returns the corresponding value from column C.
The most common cause: an invalid column number
A zero or negative column number is the standard VLOOKUP-specific cause of #VALUE!. With the same cell layout—lookup value in E2 and source table in A2:C100—the following formula asks VLOOKUP to return column zero, which is not allowed:
=VLOOKUP(E2,A2:C100,0,FALSE)
Change the third argument to the position of the column you actually want. A positive number alone is not enough: it must refer to a column included in the selected table range. If a formula uses a cell reference for the column number, check that referenced cell too. It may be blank, contain text, or evaluate to zero. For instance, a formula that calculates the column position can become invalid after someone changes a heading or edits the range.
A column number greater than the number of columns in the table generally produces #REF!, not #VALUE!. For a three-column range, 4 is outside the range. This distinction helps narrow down the cause: zero or a negative value points to #VALUE!, while an index beyond the table width points to #REF!.
Check the formula from left to right
When the error is not obvious, examine each argument in order rather than changing several parts at once. Select the formula cell and inspect the formula bar. You can also use Formulas > Evaluate Formula to follow how Excel evaluates the expression step by step. These checks are useful:
- Lookup value: Confirm that the referenced cell contains the intended value and is not itself displaying an error.
- Table range: Confirm that the first column contains the values being searched and that the return column is inside the range.
- Column number: Confirm it is a positive number and matches the return column’s position from the range’s left edge.
- Match setting: Use FALSE for an exact match unless you specifically need approximate matching.
- Referenced formulas: Trace any input cells that contain formulas; an upstream error may be flowing into VLOOKUP.
If a formula is long, temporarily select or evaluate each referenced part. The goal is to identify whether the error starts in VLOOKUP’s arguments or appears earlier in a dependent cell. Do not assume every #VALUE! displayed beside a VLOOKUP formula was generated by its column index.
Repair a copied or dynamically built formula
Copying a formula can change relative references. For example, if the lookup range is not locked, filling the formula down may shift that range and leave the return column outside it. Use dollar signs to keep a range fixed when appropriate. With lookup values in E2:E100 and a source table in A2:C100, this formula fixes the table range while allowing the lookup value to change by row:
=VLOOKUP(E2,$A$2:$C$100,3,FALSE)
When the column number comes from another cell, check the calculation and the cell’s displayed value. A heading lookup can be more maintainable than typing a number, but its result still has to be a valid position within the table. If columns are inserted or the range changes, verify that the index calculation still points to the intended return field.
Also check whether the formula has been assembled as text or includes unintended characters. A formula argument containing a text label such as Price is not the same as the numeric position of the Price column. If a cell stores a number as text, Excel may not treat it as the numeric index expected by the function. Convert or correct that input rather than hiding the problem with an error-handling wrapper.
Do not confuse #VALUE! with other lookup results
Lookup errors have different meanings, and the visible error is a useful clue:
#VALUE!: Often an invalid column index, such as zero, or an error passed from an input or dependent calculation.#N/A: VLOOKUP did not find a match under the selected matching rules. With FALSE, check spelling, spaces, data type, and whether the value exists in the first table column.#REF!: The return-column index is beyond the number of columns in the selected range, or a referenced range has been deleted or broken.#NAME?: Excel may not recognize a function name or text in the formula, often because of a spelling or syntax problem.
For exact-match VLOOKUP, a number and a number stored as text may fail to match even if they look alike on screen. Extra spaces can also make two text entries different. Those situations commonly lead to #N/A, not an invalid-index #VALUE!. Fixing the source values is more reliable than changing the return-column argument to try to suppress an unrelated error.
Use error handling only after fixing the cause
Functions such as IFERROR can replace an error with a message, but they do not repair the lookup. If the formula has an invalid index, hiding the error may make a workbook look complete while returning misleading output. Prefer a specific response for a genuinely missing match when that is the intended behavior.
With the lookup value in E2 and the source table in A2:C100, this formula displays Not found only when an exact lookup returns #N/A:
=IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found")
IFNA is narrower than IFERROR: it handles the not-found result without concealing other errors such as an invalid column index. If the workbook needs to show a blank for missing matches, use an empty text result instead of the message, but make that choice deliberately. A blank can be hard to distinguish from a valid blank value returned by the table.
When another lookup function is a better fit
VLOOKUP has a structural limitation: the value to search for must be in the first column of the table range, and the return column is specified by a number. That design is a frequent source of incorrect indexes, especially after columns are rearranged. XLOOKUP can search one range and return from another, so it does not need a column index and can return values to the left or right of the search range.
With product codes in A2:A100, prices in C2:C100, and the requested code in E2, Microsoft 365 or Excel 2021 and later can use:
=XLOOKUP(E2,A2:A100,C2:C100,"Not found")
XLOOKUP uses exact matching by default, and the optional fourth argument supplies a result when there is no match. It avoids this particular VLOOKUP column-index mistake, but it cannot fix bad source data or errors in the lookup value. Excel 2019 and earlier do not include XLOOKUP; VLOOKUP or INDEX with MATCH are options in those versions. If a workbook must be opened by people using older Excel editions, confirm function compatibility before replacing formulas.
Frequently Asked Questions
Why does VLOOKUP return #VALUE! when the value exists?
The match may exist, but VLOOKUP can still fail if the third argument is zero, negative, or otherwise invalid. Check the column index and confirm that it counts from the first column of the selected range. If the index is correct, inspect the lookup cell and source formulas for an upstream error.
Can a column number that is too large cause #VALUE!?
Usually, an index larger than the number of columns in the table range returns #REF!, while an index below 1 returns #VALUE!. For example, an index of 4 is outside a three-column range. Count the columns included in the formula’s actual range, not the worksheet column letters.
How do I fix #VALUE! in a VLOOKUP copied down a column?
Check whether the table range shifted as the formula was filled down. If it should remain fixed, use absolute references such as $A$2:$C$100, while leaving the lookup cell relative if each row has a different lookup value. Then confirm the column index is still valid.
Will changing FALSE to TRUE fix the error?
No. FALSE requests an exact match and TRUE requests approximate matching; neither corrects an invalid column index. Approximate matching also has different requirements: the first column should be sorted in ascending order for reliable results. Use the match mode that fits the data rather than changing it to mask an error.
Why does VLOOKUP show #N/A instead of #VALUE!?#N/A generally means the requested value was not found under the matching rules. Check that the lookup value appears in the first column of the range and that numbers and text, spaces, and spelling are consistent. It points to a different problem than an invalid return-column index.
Prevent the error in shared workbooks
Make lookup formulas easier to audit by keeping the lookup range consistent, using exact matching where appropriate, and documenting which field the returned column represents. When a workbook has many formulas, consider using an Excel table so the source range can expand as rows are added; still verify that the lookup column and return field are correct. Before distributing a workbook, test a known match, a missing value, and a row near the bottom of the source data. Those checks can reveal a bad index, a shifted range, or missing records before the results are relied on.
Related
- Room List Template Excel: How to Build and Use One
- Resource List Excel Template: Set Up, Track, and Share
- Replace in Excel: Find and Replace Text, Values, and More
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel #NAME? Error: What It Means and How to Fix ItThe Excel #NAME? error means Excel cannot recognize something written in a formula, such as a function name,…
- Excel ROUND Function Not Working? Fixes to TryYour Excel ROUND function usually appears not to work because the value is stored as text, the formula…
- Excel Share Workbook Not Working? Fix Sharing IssuesIf Excel’s sharing is not working, save the workbook to OneDrive or SharePoint, make sure you are signed…
- Excel AutoFit Column Width Not Working? Causes and FixesIf Excel AutoFit column width is not working, select the affected column or columns and choose Home >…
- F4 Dollar Sign in Excel Not Working? Causes and FixesIf F4 will not add dollar signs to a cell reference in Excel for Microsoft 365 on Windows,…
- Excel Insert Row Shortcut Not Working? Fix ItIf Excel’s insert-row shortcut is not working, first exit cell-editing mode, select the row or cell where the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.