WPS Spreadsheets INDEX MATCH: Fix Wrong Values in a Two-Dimensional Range
Question details
The user needs to correct an INDEX MATCH formula that returns an unexpected value from a two-dimensional range.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A two-dimensional INDEX MATCH lookup is expected to find a label such as Green-Lateral, but the source data contains an invisible trailing space.
- Observed behavior
- The formula returns the wrong value from column E because the lookup label does not exactly match the source label.
Open the source worksheet and compare the lookup label with the matching source cell character by character. Select the formula cell and identify both MATCH functions so you can verify which lookup is failing.
Remove the trailing space from the source lookup label
Use this when only one or a few source labels contain hidden spaces and you want to correct the original data directly.
INDEX MATCH requires an exact text match unless the formula is designed otherwise. A trailing space makes Green-Lateral different from Green-Lateral followed by a space, so MATCH can select an unexpected result or fail to find the intended label.
In WPS Spreadsheets, open Sheet2 and click cell E2, or the source cell containing the lookup label with the extra space.
Click the formula bar or press F2, place the cursor after the label, press Backspace to remove the trailing space, and press Enter.
Return to the INDEX MATCH formula cell and press Enter or use Formulas > Calculate Now to recalculate the result.
Select each MATCH portion in the formula bar and press F9 to preview its returned position. Press Esc afterward so the formula is not replaced by the preview value.
Clean lookup labels with TRIM in a helper column
Use this when many labels may contain leading or trailing spaces and editing each source cell individually is impractical.
Use WPS Spreadsheets to Clean Lookup Data and Repair INDEX MATCH
WPS Spreadsheets provides formula editing, calculation, and data-cleaning tools to inspect INDEX MATCH results, remove hidden spaces, and verify the correct value in a two-dimensional range.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheets, and open the workbook containing the INDEX MATCH formula.
- 2. Inspect the source label: Open Sheet2, select the source label cell, and use the formula bar or F2 to remove any leading or trailing spaces.
- 3. Clean repeated issues: Enter =TRIM(E2) in a helper column, fill it down, and update the MATCH range to use the cleaned labels.
- 4. Validate the formula: Select each MATCH expression in the formula bar, press F9 to inspect its result, press Esc, and then recalculate the worksheet.
Frequently Asked Questions
Why does INDEX MATCH return the wrong value when the lookup text looks identical?
The source label may contain a leading or trailing space or another invisible character. Select the source cell on Sheet2, press F2, and inspect the end of the text, or use =TRIM(E2) in a helper column.
How can I check which MATCH function is causing the problem?
Click the formula cell, select one MATCH expression in the formula bar, and press F9. WPS Spreadsheets displays the position returned by that MATCH. Press Esc afterward to preserve the original formula, then test the other MATCH expression.
Can TRIM fix all hidden spaces in an INDEX MATCH lookup?
TRIM removes ordinary leading and trailing spaces and normalizes repeated spaces. If the data contains nonbreaking spaces from a website or PDF, use SUBSTITUTE with CHAR(160), or replace the characters manually before running the lookup.
Should I edit the original label or change the INDEX MATCH formula?
If only one or two labels are affected, remove the extra space from the original source cell. If many labels are inconsistent, create a TRIM helper column and point the MATCH range to the cleaned values.
