WPS Spreadsheet: Return Matching Text with INDEX and MATCH
Question details
The user needs to match each value entered in column D with a list in column M and return the corresponding retention text from column N.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet uses column D for entered lookup values, column M for the matching list, and column N for the text that should be returned in column J.
- Observed behavior
- The matching retention text needs to populate automatically for each value entered in column D.
Confirm that the lookup values in column M and the return text in column N cover the same rows, such as M2:M77 and N2:N77. Make sure the values in column D use the same spelling and formatting as the lookup list.
Use INDEX and MATCH to return the matching retention text
Use this solution when column D contains the lookup value, column M contains the matching list, and column N contains the text to return.
MATCH searches for the value from column D in the lookup range and returns its relative position. INDEX uses that position to retrieve the corresponding text from column N.
Select cell J2 and enter =INDEX($N$2:$N$77,MATCH(D2,$M$2:$M$77,0)). Press Enter to return the text associated with the value in D2.
Select J2, then drag the fill handle down the column or copy and paste the formula into the remaining cells. The relative reference D2 changes for each row, while the absolute lookup ranges stay fixed.
Hide missing-match errors with IFERROR
Use this variation when unmatched values should show a blank or custom message instead of #N/A.
Complete INDEX and MATCH lookups in WPS Spreadsheet
WPS Spreadsheet lets you build the exact lookup formula, fill it down, and return matching text from a related column. Its familiar formula tools support smooth work with existing Excel worksheets.
- 1. Open the worksheet: Launch WPS Office, open WPS Spreadsheet, and load the worksheet containing columns D, M, and N.
- 2. Select the output cell: Click cell J2, or the first cell where the matching retention text should appear.
- 3. Enter the formula: Type =INDEX($N$2:$N$77,MATCH(D2,$M$2:$M$77,0)) in the formula bar and press Enter.
- 4. Fill down the results: Drag the fill handle from J2 down the output column to apply the lookup to the remaining rows.
Frequently Asked Questions
What formula returns the text associated with a value in column D?
In J2, enter =INDEX($N$2:$N$77,MATCH(D2,$M$2:$M$77,0)). This searches for D2 in M2:M77 and returns the corresponding value from N2:N77.
Why does the formula show #N/A in WPS Spreadsheet?
#N/A means the value in D2 was not found in M2:M77. Check for spelling differences, extra spaces, or mismatched number and text formatting.
How can I apply the INDEX MATCH formula to every row?
After entering the formula in J2, select the cell and drag its fill handle down, or copy J2 and paste it into the cells below. The D2 reference updates by row while the lookup ranges remain fixed.
How do I display a blank when no matching value exists?
Use =IFERROR(INDEX($N$2:$N$77,MATCH(D2,$M$2:$M$77,0)),"") in J2, then fill the formula down the column.
