Fix VLOOKUP and INDEX MATCH Problems with Formula-Generated Values in WPS Office Spreadsheets
Question details
The user needs to make VLOOKUP and INDEX MATCH return correct results when the lookup values are generated by formulas in a consolidated range containing both text and numbers.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is looking up values in an Excel 2019-compatible workbook. The lookup range consolidates text and numbers, and the lookup values are generated by formulas instead of being typed manually.
- Observed behavior
- Manually typed lookup values match correctly, but formula-generated lookup values fail to find matches, causing #N/A or incorrect results.
Before editing the formulas, set the workbook calculation mode to Automatic via Formulas > Calculation Options, and insert a few helper columns so you can compare normalized values side by side with the original lookup range.
Normalize Lookup and Source Values with TRIM, CLEAN, and VALUE
Use this when lookup values are generated by formulas and the source range mixes text and numbers; it removes spaces and hidden characters and unifies data types.
VLOOKUP and INDEX MATCH compare values exactly. A formula-generated value may include hidden spaces or be stored as text, while the source value is a number or has different padding. Normalizing both sides before lookup resolves most mismatches.
Select the first empty cell in the helper column next to your formula-generated lookup values and enter =TRIM(CLEAN(A2)). Adjust A2 to the first lookup cell, then fill the formula down.
Next to the source column, enter =TRIM(CLEAN(D2)) in another helper column and fill it down so every value in the lookup range is stripped of extra spaces and nonprinting characters.
If a helper value is numeric but stored as text, change its formula to =VALUE(TRIM(CLEAN(A2))) or =--TRIM(CLEAN(A2)). Apply the same conversion to the matching source helper column.
Change the lookup formula to reference the helper columns, for example =INDEX(return_range,MATCH(TRIM(CLEAN(A2)),source_helper_range,0)). Press Enter and then Formulas > Calculate Now to recalculate.
Audit Hidden Mismatches with EXACT and LEN Helper Columns
Use this when the lookup still fails after normalization; it reveals whether values only look identical but contain different characters or trailing spaces.
Fix Lookup Formula Mismatches in WPS Office Spreadsheets
Open your Excel 2019 workbook in WPS Office Spreadsheets, normalize the formula-generated lookup values and source range with TRIM, CLEAN, and VALUE, and update the INDEX MATCH or VLOOKUP references. WPS keeps your formulas compatible and your workbook editable for free.
- 1. Open the workbook: Launch WPS Office Spreadsheets and open the affected Excel 2019 workbook from the File > Open menu.
- 2. Enable automatic calculation: Go to Formulas > Calculation Options and select Automatic so formula-generated values refresh after edits.
- 3. Create helper columns: Insert columns next to the lookup and source ranges, then enter =TRIM(CLEAN(cell)) and =VALUE(TRIM(CLEAN(cell))) as needed to normalize text and numbers.
- 4. Update the lookup formula: Modify the VLOOKUP or INDEX MATCH formula to reference the normalized helper columns, then press Enter.
- 5. Save in Excel-compatible format: Click File > Save As and choose .xlsx to keep the workbook compatible with Microsoft Excel.
Frequently Asked Questions
Why does VLOOKUP work with typed values but fail with formula-generated values?
Formula-generated values can be stored as text or include hidden characters, while manually typed values are often recognized as numbers. Use =ISTEXT(A2) and =ISNUMBER(A2) to check the format, then apply TRIM, CLEAN, or VALUE to normalize both sides.
How can I tell if a lookup value is text or a number in WPS Spreadsheets?
Select the cell and check the Home tab number format, or use =ISTEXT(A2) and =ISNUMBER(A2). Text numbers are usually left-aligned and numbers are right-aligned by default.
What is the fastest formula to remove hidden spaces before a lookup?
Wrap the lookup reference in =TRIM(CLEAN(A2)). For nonbreaking spaces, use =SUBSTITUTE(A2,CHAR(160),""). Apply the same cleanup to the lookup range so both sides match.
Does INDEX MATCH handle text and number mismatches better than VLOOKUP?
INDEX MATCH is not automatically immune to data type mismatches, but it is more flexible because the lookup range can be in any column. You still need to normalize text and numbers with VALUE or -- before matching.
Can WPS Office free version correct Excel 2019 lookup formula issues?
Yes. WPS Office Spreadsheets can open the workbook, recalculate formulas with Formulas > Calculate Now, and use the same TRIM, CLEAN, VALUE, INDEX, and MATCH functions to repair lookup mismatches.
