Return All Rows for Volunteers with a Specific Disease Code in WPS Spreadsheets
Question details
The user needs to identify all rows associated with volunteers who have a specified disease code while preserving the volunteers' other disease code rows.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Filtering related records in a spreadsheet where each volunteer ID may appear with multiple disease codes.
- Observed behavior
- The filtered result should show every row for any volunteer with the specified disease code, including rows that contain other disease codes.
Make sure your worksheet has a volunteer ID column, for example column A, and a disease code column, column B, and enter the target disease code in a separate cell such as G2.
Use a helper column with MATCH and IFNA to mark qualifying volunteer IDs
Use this method when you need a compatible formula for older Excel versions or WPS Spreadsheets and want to keep all rows for qualifying volunteers.
The formula checks whether the current row's volunteer ID appears anywhere with the target code in G2. When a match exists, it writes the volunteer ID in the helper column; otherwise it returns an empty value. Filling the formula down marks every row for volunteers who qualify, so you can filter by the helper column.
Click cell C2, or the first empty column next to your data, to start the helper formula.
Type =IFNA(IF(MATCH(1,($A$2:$A$15=$A2)*($B$2:$B$15=$G$2),0),A2,""),"") in C2.
In legacy array formula versions, press Ctrl+Shift+Enter; otherwise press Enter. Then use the fill handle to copy the formula down through all data rows.
Apply a filter and show only rows where column C is not blank to return all rows for volunteers with the specified disease code, including their other disease codes.
Use the FILTER function with a qualifying volunteer ID list
Use this method when your version supports dynamic arrays and you want a single complete filtered result without a manual filter.
Filter volunteer disease-code records in WPS Office Spreadsheets
Use WPS Office Spreadsheets to mark qualifying volunteer IDs with a helper formula or return a dynamic filtered list, so every row for a volunteer with the target disease code stays visible.
- 1. Open your workbook: Open the volunteer and disease code workbook in WPS Office Spreadsheets.
- 2. Enter the target code: Type the specific disease code you want to match in cell G2.
- 3. Add the helper formula: In C2, enter =IFNA(IF(MATCH(1,($A$2:$A$15=$A2)*($B$2:$B$15=$G$2),0),A2,""),"") and fill it down.
- 4. Filter nonblank rows: Apply a filter to the helper column and show only nonblank cells to keep all rows for qualifying volunteers.
- 5. Review or extract the result: Copy or review the filtered rows; they include the target disease code and the volunteer's other disease codes.
Frequently Asked Questions
Why does the helper formula return a volunteer's other disease code rows?
The formula checks whether the current row's volunteer ID appears anywhere with the target code in G2. If yes, it marks the volunteer ID even on rows where that row has a different disease code, so filtering nonblank helper cells keeps all related rows.
Does this formula work in older Excel or WPS Spreadsheets?
Yes. Use the helper column formula and press Ctrl+Shift+Enter in legacy array formula versions. If IFNA is not available, replace IFNA with IFERROR for the same result.
How do I adjust the formula for a larger dataset?
Change $A$2:$A$15 and $B$2:$B$15 to your actual data range, then fill the formula down through every data row. Ensure the target disease code cell remains absolute, such as $G$2.
What if FILTER is not available in my WPS Office version?
Use the helper column method with MATCH and IFNA. The FILTER function requires a version that supports dynamic arrays; the helper method works in older versions.
Can I return only the first matching row instead of all rows?
Yes, you can use a MATCH result directly to locate the first row, but to return every row for the qualifying volunteer you should use the helper column or FILTER approach described here.
