How to Check a Closed Excel Workbook with MATCH and ISNUMBER in WPS Office
Question details
The user needs an alternative to COUNTIF that can check whether an employee ID exists in another Excel workbook when the external workbook is closed.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user manages employee IDs and needs to verify whether an ID in one workbook exists in a master monitoring workbook without keeping the external file open.
- Observed behavior
- COUNTIF returns correct results only while the external workbook is open, so the user needs a formula that works with a closed workbook reference.
Before writing the formula, confirm the full file path and sheet name of the closed workbook, and make sure the employee ID column is in column A or adjust the range to match your data.
Use MATCH with ISNUMBER to test a closed workbook reference
Use this formula when COUNTIF only works while the external workbook is open and you need an exact employee ID match.
MATCH returns the row position of an exact employee ID and ISNUMBER converts that result to TRUE or FALSE, so IF can return Y or blank. External references to closed workbooks may still be limited by the workbook setup, but this approach avoids COUNTIF's open-workbook restriction.
In WPS Office Spreadsheets, open the workbook where you want the result and click the cell next to the employee ID you need to check.
Type =IF(ISNUMBER(MATCH([@EmployeeID],'filelocation/[MVR Monitoring Batch File (MASTER).xlsx]Sheet1'!$A:$A,0)),"Y","") and replace filelocation with the actual folder path, file name, and sheet name as needed.
Press Enter to calculate the formula. The cell shows Y when the employee ID exists in the closed workbook and nothing when it does not.
Check IDs in a Closed Workbook with WPS Office Spreadsheets
Open the workbook in WPS Office Spreadsheets, use the MATCH and ISNUMBER formula to test employee IDs against a closed Excel workbook, and get Y or blank results without keeping the source file open.
- 1. Download and open WPS Office Spreadsheets: Download WPS Office, launch WPS Office Spreadsheets, and open the destination workbook that contains the employee IDs to check.
- 2. Select the result cell: Click the first cell where you want to display Y or blank for the employee ID.
- 3. Enter the MATCH/ISNUMBER formula: Type =IF(ISNUMBER(MATCH([@EmployeeID],'filelocation/[MVR Monitoring Batch File (MASTER).xlsx]Sheet1'!$A:$A,0)),"Y","") and replace the file path and sheet name with your closed workbook location.
- 4. Press Enter and copy the formula: Press Enter to calculate the result, then use the fill handle to copy the formula down to the remaining employee IDs.
Frequently Asked Questions
Why does COUNTIF return an error or zero for a closed Excel workbook?
COUNTIF and other conditional aggregation functions such as SUMIF and COUNTIFS are not designed to evaluate references to closed workbooks. Use MATCH, VLOOKUP, or INDEX/MATCH instead, which can often read the closed workbook reference.
Can MATCH work with a closed workbook in WPS Office Spreadsheets?
Yes. In WPS Office Spreadsheets you can use MATCH with a full file path, workbook name, sheet name, and range. If the link cannot be updated, open the source workbook once to refresh the external reference.
How do I change the formula if the ID column is not column A?
Replace $A:$A with the actual column range, such as $B:$B, and make sure the lookup value and the range contain the same type of employee ID text or number.
How can I avoid updating links every time I open the workbook?
Check Data > Edit Links in WPS Office Spreadsheets and set the prompt or update behavior for external links, or keep source and destination workbooks in the same folder with the same file name.
What does the empty result mean in the IF formula?
The formula returns blank when no exact match is found. If you prefer a visible N result, replace the empty string "" with "N" in the IF formula.
