Skip to content
Excel 📅 2026-02-11

How to Resolve VLOOKUP Lookup Value Exceeds 255 Characters Error

As a data analyst, encountering a #VALUE! error when performing a VLOOKUP can be perplexing, especially when the data appears correct. A common, yet often overlooked, cause for this is when your lookup value exceeds Excel's internal 255-character limit for VLOOKUP and MATCH functions. This article provides robust solutions to ensure your lookups function flawlessly, regardless of string length.

🚨 Symptoms & Diagnosis

You'll typically observe one of the following error signatures in your worksheet:

#VALUE!

VLOOKUP fails when a lookup value exceeds 255 characters

#N/A (with wildcard mismatch on long strings)

Root Cause: Excel's lookup function engine (VLOOKUP/MATCH subsystem) has an internal string length restriction in its comparison logic. This limitation rejects lookup values exceeding 255 characters, triggering a #VALUE! error, and can also cause wildcard searches to fail on target strings longer than this threshold.

🛠️ Solutions

Immediate Mitigation: INDEX/MATCH Replacement

INDEX/MATCH is a powerful and flexible alternative to VLOOKUP that bypasses the 255-character lookup value limitation by performing array-based comparisons directly. This method is ideal for exact matches on long strings.

  1. Select the target cell where you want the lookup result.
  2. Enter the following formula, replacing B2:B10 with your result range, A$2:A$10 with your lookup column, and E1 with your lookup value cell: 
    =INDEX(B2:B10,MATCH(TRUE,TRIM(A$2:A$10)=TRIM(E1),0))
    Note: TRIM is used here for robustness, removing any leading/trailing spaces that might cause mismatches.
  3. For legacy Excel versions (2019 and earlier), press Ctrl+Shift+Enter to enter it as an array formula. For Excel 365 or Excel 2021, simply press Enter, as these versions handle dynamic arrays automatically.
  4. Verify the fix by checking LEN(lookup_cell) to confirm it exceeds 255 characters, and that your INDEX/MATCH formula now returns the correct value instead of #VALUE!.

Immediate Mitigation: SEARCH Function for Wildcards

When attempting wildcard-like searches (*, ?) with long strings, VLOOKUP can fail. The SEARCH function offers a reliable way to locate substrings within longer strings, which can then be integrated into an INDEX/MATCH structure.

  1. In a helper column (e.g., column C if your data is in A and B), apply the SEARCH function to identify if your lookup value exists as a substring in your data. Replace E1 with your lookup value and A2 with the cell in your lookup column: 
    =IF(ISNUMBER(SEARCH(E1,A2)),"Found","Not Found")
    This formula returns "Found" if the lookup value E1 is found within cell A2, otherwise "Not Found".
  2. Copy this formula down for all relevant rows in your dataset.
  3. You can then use the result of this helper column with INDEX/MATCH to perform your lookup. For example, if your helper column is C and you're looking for "Found": 
    =INDEX(B:B,MATCH("Found",C:C,0))
  4. To audit your formulas and ensure correct application, navigate to the Home tab on the Ribbon, then Find & Select > Go To Special > Formulas.

Best Practice Fix: Custom VBA VLOOKUP Function

For recurring long-string lookups or scenarios where INDEX/MATCH complexity becomes unwieldy, a custom User-Defined Function (UDF) written in VBA provides a permanent, reusable solution without the 255-character limitation.

  1. Open the VBA editor by pressing Alt+F11.
  2. In the Project Explorer (usually on the left), right-click on ThisWorkbook for your current Excel file.
  3. Select Insert > Module.
  4. Paste the following VBA code into the new module window: 
    Function MyVlookup(Lval As Range, c As Range, oset As Long) As Variant
    Dim i As Long
    For i = 1 To c.Rows.Count
        ' Direct string comparison, bypassing VLOOKUP's internal limits
        If c.Cells(i, 1).Value = Lval.Value Then
            MyVlookup = c.Cells(i, oset).Value
            Exit Function
        End If
    Next i
    MyVlookup = CVErr(xlErrNA) ' Return #N/A if no match found
End Function
  5. Save your Excel workbook as an Excel Macro-Enabled Workbook (.xlsm) to preserve the VBA code.
  6. You can now use =MyVlookup(F1,A1:B20,2) directly in any cell in your worksheet, where F1 is your lookup value, A1:B20 is your table array (lookup column must be the first column), and 2 is the column index from which to return a value.

🧩 Technical Context (Visualized)

The core VLOOKUP and MATCH functions within Excel's lookup engine are designed with an internal buffer or comparison logic that limits the length of the string value they can effectively process as a lookup criterion. When a lookup value exceeds this 255-character threshold, the engine simply cannot perform the comparison, leading to an immediate error state. This is distinct from a mismatch; it's a structural limitation in how the lookup is attempted.

graph TD
    A[Start VLOOKUP/MATCH Function] --> B{Is Lookup Value Length > 255 Characters?};
    B -- Yes --> C[VLOOKUP/MATCH Internal Engine Restriction Triggered];
    C --> D[Comparison Logic Fails Immediately];
    D --> E["Output: #VALUE! Error"];
    B -- No --> F[VLOOKUP/MATCH Continues Normal Processing];
    F --> G{Match Found in Table Array?};
    G -- Yes --> H[Return Result];
    G -- No --> I["Output: #N/A Error"];

✅ Verification

To confirm your fix is working as expected:

  1. In an empty cell, enter =LEN(A1) (assuming A1 contains your long lookup value) to confirm that the length indeed exceeds 255 characters.
  2. Replace your original VLOOKUP formula with one of the provided fix formulas (INDEX/MATCH, SEARCH combination, or MyVlookup).
  3. Check the cell for a valid return value. The presence of a correct result, rather than #VALUE!, indicates a successful resolution.
  4. Press F9 to recalculate your worksheet, ensuring all formulas are updated.

📦 Prerequisites

  • Excel 2010 or newer: For general formula functionality.
  • Excel 365 / Excel 2021: For dynamic array behavior with INDEX/MATCH (simplifies array formula entry).
  • VBA for .xlsm: If using the custom MyVlookup function, your workbook must be saved as an .xlsm file. You also need to enable macros via File > Options > Trust Center > Trust Center Settings > Macro Settings > Enable VBA macros.