How to Return Excel Values for Codes with a Specific Prefix in WPS Office
Question details
User needs a formula to extract the two-digit prefix from a code and return matching values from a column instead of comparing the full code to a text range.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet has codes composed of a two-digit prefix plus a three-digit number, and the user wants to list values whose prefix matches a condition.
- Observed behavior
- Comparing the whole code against a text range fails because the prefix is embedded in a longer code and must be extracted and compared separately.
Confirm that the code column is consistent, with the target prefix always as the first two characters, and enter the required prefix in a helper cell such as G5 before applying the formula.
Use an INDEX-SMALL-IF array formula to extract matching values by prefix
Use this formula when you need a copy-down solution that works in WPS Spreadsheets and older Excel versions without dynamic arrays.
The formula uses LEFT to isolate the first two characters, NUMBERVALUE to convert them to a number, and IF to compare against the value in G5. SMALL and INDEX then retrieve the matching values from the target column in row order.
Type the two-digit prefix to match, such as 39, into cell G5.
Select the first cell where results should appear and enter =IFERROR(INDEX($D$7:$D$178,SMALL(IF(NUMBERVALUE(LEFT($B$7:$B$178,2))=$G$5,ROW($B$7:$B$178)-6),ROW(A1))),"").
In older Excel versions, confirm the formula with Ctrl+Shift+Enter. In current WPS Spreadsheets, enter it normally if array evaluation is automatic.
Drag the fill handle down until blank cells appear, indicating that no more matching values are available.
Use the FILTER function for a modern spill formula
Use this method if your version supports dynamic arrays and you want all matching values to spill automatically without dragging the formula down.
Return Values by Prefix with WPS Spreadsheets
Use Excel-compatible functions in WPS Spreadsheets to isolate the first two characters of each code, match the prefix in G5, and return the related value without comparing the full code.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the Excel or .et workbook that contains the code and value columns.
- 2. Enter the target prefix: Type the two-digit prefix, such as 39, into cell G5.
- 3. Apply the prefix lookup formula: In the first result cell, enter the INDEX-SMALL-IF formula or the FILTER formula that uses LEFT and IF to compare only the first two characters.
- 4. Copy or allow spill: Drag the formula down or let dynamic array results spill to list every matching value.
- 5. Verify the output: Check the returned values against the code column to confirm that all rows with the prefix are captured.
Frequently Asked Questions
Why does comparing the full code to the prefix fail?
Because codes are longer strings such as 39123, the prefix 39 is only part of the value. Use LEFT(cell,2) or NUMBERVALUE(LEFT(cell,2)) to isolate the first two characters before comparing with the criteria cell.
Do I have to press Ctrl+Shift+Enter in WPS Spreadsheets?
Many current WPS Spreadsheets versions evaluate array formulas automatically. If the formula returns an error or only one result, try the array confirmation shortcut, or use the FILTER spill formula in supported versions.
How do I return values from a different column?
Change the INDEX range from $D$7:$D$178 to the column containing the values you want, and adjust ROW($B$7:$B$178)-6 so the row offset matches the starting row of your data.
Can I match the prefix as text instead of a number?
Yes. If prefixes may contain leading zeros, compare LEFT($B$7:$B$178,2) with a text-formatted G5 using =IFERROR(INDEX($D$7:$D$178,SMALL(IF(LEFT($B$7:$B$178,2)=$G$5,ROW($B$7:$B$178)-6),ROW(A1))),"").
