WPS Spreadsheet: Check Column Values Against a Dynamic List
Question details
The user needs to return TRUE or FALSE for each value in a large column according to whether that value exists in a dynamically changing list.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A target column must be checked against a selected or formula-generated list whose contents can change over time.
- Observed behavior
- Each target value should display TRUE when it is found in the dynamic list and FALSE when it is not found.
Identify the target column and the cell or named range that contains the dynamic list. If the list is generated by a spilling formula, confirm its spill anchor cell, such as H2, and decide how blank target cells should be handled.
Use XMATCH, ISNA, and NOT to test dynamic list membership
Use this method when the selected values are stored in a dynamic range or a formula-generated spill list and each target value needs a TRUE or FALSE result.
XMATCH searches for the target value in the list and returns a position when it finds a match. When no match exists, XMATCH returns #N/A; ISNA detects that result, and NOT converts it into TRUE for a match and FALSE for a non-match.
In WPS Spreadsheet, select the first result cell beside the target column, such as B2, next to the value in A2.
Enter =NOT(ISNA(XMATCH(A2,$H$2#,0))) and press Enter. Replace A2 with the first target value and $H$2# with the spill range beginning at the dynamic list's anchor cell.
Drag the fill handle from the result cell down the target column, or copy the formula and use Home > Paste to apply it to the remaining rows.
If the list has a defined name, replace $H$2# with that name, such as =NOT(ISNA(XMATCH(A2,selected_list,0))). Use exact-match mode 0 to avoid partial or approximate matches.
Use COUNTIF or COUNTIFS for a simpler membership check
Use this alternative when the list is a normal range or named range and you only need a presence test without XMATCH error handling.
Check Dynamic List Membership with WPS Spreadsheet Formulas
WPS Spreadsheet can compare a large target column with a changing selection list using XMATCH, ISNA, and NOT. Enter the formula once, reference a spilling list or named range, and fill it down to produce reliable TRUE or FALSE results.
- 1. Open the workbook: Launch WPS Spreadsheet and open the workbook containing the target column and dynamic selection list.
- 2. Choose the result column: Click the first empty cell beside the target value column where the membership result should appear.
- 3. Enter the XMATCH formula: Enter =NOT(ISNA(XMATCH(A2,$H$2#,0))), replacing A2 and $H$2# with the target cell and dynamic list anchor used in your workbook.
- 4. Apply the formula to all rows: Press Enter, then drag the fill handle down or use Home > Fill > Down to check the complete column.
Frequently Asked Questions
How do I check one Excel column against a dynamic spill list in WPS Spreadsheet?
If the list starts in H2 and spills downward, enter =NOT(ISNA(XMATCH(A2,$H$2#,0))) beside the first target value, then fill the formula down. The # reference expands as the spill list changes.
Why does XMATCH return #N/A for some values?
#N/A means XMATCH did not find an exact match in the referenced list. Check for extra spaces, different number or text types, spelling differences, and whether the formula references the correct spill range.
Can I use a named range instead of a spill reference?
Yes. Replace $H$2# with the named range, for example =NOT(ISNA(XMATCH(A2,selected_list,0))). Confirm that the name includes the current dynamic list rather than only its original fixed cells.
How can I keep blank target rows from returning TRUE?
Wrap the test in an IF function: =IF(A2="","",NOT(ISNA(XMATCH(A2,$H$2#,0)))). This leaves empty target rows blank while returning TRUE or FALSE for populated rows.
