WPS Spreadsheet: Fix INDEX MATCH Returning an Asterisk
Question details
The user needs to correct an INDEX/MATCH formula that returns an asterisk for certain product and fiber combinations.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The formula retrieves messages based on a product or fiber combination, but the INDEX return range and MATCH lookup range contain different numbers of rows.
- Observed behavior
- The formula works for some combinations but returns an asterisk for others, especially when the lookup result points beyond the INDEX range or the suffix is missing.
Open the worksheet in WPS Spreadsheet and identify the cells containing the lookup value, suffix list, message list, and the current INDEX/MATCH formula. Count the rows in both ranges and confirm that the lookup table includes the required suffixes, such as SMF and MMF.
Match the INDEX and MATCH ranges in WPS Spreadsheet
Use this solution when the formula uses ranges with different heights or when MATCH can return a row number outside the INDEX range.
MATCH returns a relative position, and INDEX uses that position to select a value. The INDEX return range and MATCH lookup range must have the same number of rows so the returned position points to a valid result.
In WPS Spreadsheet, click the cell that displays the asterisk and review its formula in the formula bar.
Edit the formula so both ranges cover the same rows. For example, if the suffixes are in I28:I32 and the messages are in K28:K32, enter =INDEX(K28:K32,MATCH(J22,I28:I32,0)).
Keep the MATCH argument 0 to require an exact match, then press Enter and check the result for the affected product and fiber combination.
Change the value in J22 or select another product-fiber combination and verify that each valid suffix returns the corresponding message.
Check the lookup table for missing fiber suffixes
Use this check when the ranges already have matching dimensions but only specific combinations still return an asterisk.
Resolve INDEX MATCH Range Errors with WPS Spreadsheet
WPS Spreadsheet lets you inspect and correct INDEX/MATCH formulas directly, align lookup and return ranges, and test exact matches in a familiar spreadsheet interface. It supports common Excel workbooks for a smooth transition.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the formula error.
- 2. Inspect the formula: Select the affected cell and use the formula bar to compare the INDEX return range with the MATCH lookup range.
- 3. Correct the ranges: Replace the formula with =INDEX(K28:K32,MATCH(J22,I28:I32,0)) or use equivalent same-sized ranges from your worksheet.
- 4. Verify the results: Test the affected product and fiber combinations and confirm that valid suffixes return messages instead of asterisks.
Frequently Asked Questions
Why does INDEX MATCH return an asterisk for only some combinations?
The MATCH function may return a position that is outside the smaller INDEX range when the lookup ranges have different numbers of rows. Missing or mismatched suffix text can also prevent an exact match.
What range sizes should INDEX and MATCH use?
They should cover the same number of rows and correspond to the same records. For example, use INDEX(K28:K32, MATCH(J22, I28:I32, 0)) when both ranges contain five rows.
How can I require an exact match in WPS Spreadsheet?
Add 0 as the third MATCH argument. The structure is MATCH(lookup_value, lookup_range, 0), such as MATCH(J22, I28:I32, 0).
What should I check if matching ranges do not fix the asterisk?
Compare the lookup value with every entry in the lookup range, including spaces, parentheses, and suffixes such as SMF and MMF. Correct the table text or formula so both values are identical.
