#VALUE! Error in VLOOKUP: Causes and Solutions

#VALUE!

+VLOOKUP

VLOOKUP is one of Excel's most powerful lookup functions, but it's also notorious for throwing #VALUE! errors that can halt your spreadsheet workflows. This frustrating error typically appears when VLOOKUP encounters incompatible data types, mismatched formatting, or invalid arguments that it simply cannot process. The good news? #VALUE! errors in VLOOKUP are among the easiest to diagnose and fix. Most occur due to a handful of common culprits: text stored as numbers, leading or trailing spaces in lookup values, incorrect column index numbers, or incompatible data types between your lookup value and table array. Whether you're a beginner struggling with your first VLOOKUP or an experienced user encountering an unexpected error, this guide walks you through the most likely causes and their straightforward solutions. You'll be back on track in minutes.

Why VLOOKUP causes #VALUE!

Non-numeric col_index_num argument

The col_index_num parameter must be a number representing the column position to return. If you pass text, a formula error, or a non-integer value, VLOOKUP returns #VALUE!.

=VLOOKUP("Smith",A1:D100,"Name",FALSE) where "Name" is text instead of the number 2

Lookup value contains leading/trailing spaces or data type mismatch

VLOOKUP performs an exact character match when range_lookup is FALSE. Extra spaces in the lookup_value or a mismatch between text and numbers (e.g., "123" vs 123) causes #VALUE! when the match fails and propagates through dependent formulas.

=VLOOKUP(" Product",A2:C100,2,FALSE) where column A contains "Product" without the leading space

Invalid range_lookup argument (not TRUE, FALSE, 0, or 1)

The range_lookup parameter only accepts TRUE, FALSE, 0, or 1. Passing any other value—such as text or an invalid number—triggers #VALUE!.

=VLOOKUP("Smith",A1:D100,2,"Exact") where "Exact" is invalid instead of FALSE or 0

Step-by-Step Solution

1Click on the cell displaying the #VALUE! error to select it, then look at the formula bar (Ctrl+`) to view the complete VLOOKUP formula
2Check the lookup_value (first argument) for spaces, line breaks, or formatting mismatches—use TRIM() to clean it: =VLOOKUP(TRIM(A2),range,col,FALSE)
3Verify the table_array (second argument) includes the correct range with both lookup column and return column; confirm it uses absolute references ($A$1:$D$100) if copying the formula
4Confirm the col_index_num (third argument) is a number between 1 and the total columns in your range; if it exceeds the range width, you'll get #VALUE!
5Check that range_lookup (fourth argument) is FALSE or 0 for exact matches; using TRUE with unsorted data or text values causes #VALUE! errors
6If your lookup value contains different data types (text vs. number), convert both to match using VALUE() or TEXT(): =VLOOKUP(VALUE(A2),range,col,FALSE)
7Wrap the entire formula in IFERROR() to catch remaining errors: =IFERROR(VLOOKUP(TRIM(A2),$B$2:$D$100,3,FALSE),"Not Found")
8Press Enter (or Ctrl+Shift+Enter for array formulas) and verify the corrected formula returns the expected result or your custom error message

Concrete Example

Employee salary lookup in HR database

An HR manager uses VLOOKUP to retrieve employee salaries from a master employee table. The lookup table contains Employee IDs in column A and salaries in column C. Multiple sheets are involved: 'Payroll' (where the formula is) and 'EmployeeData' (the lookup table).

Before (error)

=VLOOKUP(A2,EmployeeData!A:C,3,FALSE)

After (fixed)

=IFERROR(VLOOKUP(VALUE(A2),EmployeeData!$A$2:$C$500,3,FALSE),"Employee not found")

Problem: The #VALUE! error appears because the Employee ID in the Payroll sheet is stored as text (e.g., 'EMP-001') while the lookup table contains it as a number (1001), or the lookup range includes a header row with text that cannot be compared numerically.

Solution: Convert the lookup value to the same data type as the table array using VALUE() or TEXT(), or ensure both columns use consistent formatting. Additionally, verify the column index number is numeric and the range reference is valid.

Prevention Tip

Ensure your lookup column (first column in the table array) contains the same data type as your lookup value—convert both to text or numbers using VALUE() or TEXT() if needed, as VLOOKUP fails when comparing mismatched types like "123" text to 123 numbers.

Free Tools to Fix Your Formulas

Use these free tools to avoid this error:

Excel Formula Generator

Describe what you want to calculate and get the Excel formula instantly

VLOOKUP Generator

Generate VLOOKUP formulas instantly by describing what you need in plain English

Excel Formula Explainer

Paste any Excel formula and get a clear, step-by-step explanation powered by AI. Understand complex formulas instantly.

Tired of wrestling with #VALUE! errors in VLOOKUP? ElyxAI automatically detects and fixes formula errors like this in seconds—try it free today and get back to work.