How to Fix Excel VLOOKUP and MATCH Errors with Long Text
Question details
The user needs to locate specific codes or OEM part numbers inside long text descriptions exported from SAP, but standard lookup functions are failing.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching for partial text matches within lengthy data cells (such as SAP material descriptions) using wildcard characters.
- Observed behavior
- VLOOKUP and MATCH functions return errors or fail to find matches because the text in the lookup array exceeds the 255-character limit for wildcard searches.
Verify the length of the text in your target cells using the LEN function. If the text exceeds 255 characters, standard wildcard searches with VLOOKUP and MATCH will inherently fail and require alternative functions.
Use XMATCH for Long Text Strings
XMATCH is the recommended alternative for newer spreadsheet versions, easily overcoming the 255-character limit for wildcard searches.
The XMATCH function handles wildcard searches far more robustly than traditional MATCH and VLOOKUP functions. By wrapping the search term in wildcards, you can accurately locate substrings in lengthy text descriptions without triggering length-limit errors.
Click on the cell where you want the matching row position to appear.
Type the formula =XMATCH("*"&A2&"*", Sheet1!D$1:D$1000, 2). Replace 'A2' with the cell containing your lookup value and 'Sheet1!D$1:D$1000' with your actual target data range.
Press Enter to return the relative row position of the match.
Use an Array Formula with MATCH and SEARCH
For older spreadsheet versions that do not support XMATCH, a combination of MATCH, ISNUMBER, and SEARCH bypasses the text length limits safely.
Easily Process Long Data Strings in WPS Spreadsheet
WPS Spreadsheet features a powerful, modern formula engine that fully supports advanced lookup functions like XMATCH, INDEX, and SEARCH, allowing you to bypass standard string length limits seamlessly.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing your lengthy text descriptions.
- 2. Select a blank cell: Click the exact cell where you want to extract or locate your specific code.
- 3. Enter the lookup formula: Input the =XMATCH() or =SEARCH() formula referencing your target range and lookup value.
- 4. Apply to multiple rows: Press Enter, then double-click the fill handle at the bottom-right corner of the cell to apply the formula down the entire column.
Frequently Asked Questions
Why does VLOOKUP fail with long text strings?
VLOOKUP and MATCH have a built-in limitation where they cannot process search criteria or wildcard matches that exceed 255 characters. When a lookup value surpasses this limit, the formula typically returns a #VALUE! or #N/A error.
How do I return the actual value instead of just the row number?
To get the actual data value, wrap your XMATCH or MATCH formula inside an INDEX function. For example: =INDEX(B1:B1000, XMATCH("*"&A2&"*", D1:D1000, 2)). This uses the position found by XMATCH to extract the corresponding value from column B.
Does the SEARCH function have a character limit?
Unlike VLOOKUP, the SEARCH function can process strings containing up to 32,767 characters. This makes it an ideal workaround for finding substrings inside very long cells, such as detailed SAP material descriptions.
