Return 1 or 0 When a Value Exists on Another Excel Sheet
Question details
Check whether a value appears on another worksheet and return 1 if it does or 0 if it does not.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to compare a value in each row with a list or range on another worksheet.
- Observed behavior
- The formula should return a numeric presence indicator for each value.
Identify the cell containing the value to check and the column or range to search on the other sheet. If the sheet name contains spaces, enclose it in single quotes in the formula, such as 'Product List'!$A:$A.
Use MATCH to check a column on another sheet
Use this formula when the values to search are in one column.
In a result cell next to the value in A1, enter =--ISNUMBER(MATCH(A1,Sheet1!$A:$A,0)). Replace Sheet1 and column A with the sheet and column you want to search.
Press Enter. The formula returns 1 if MATCH finds the value and 0 if it does not.
Drag the result cell's fill handle down the column. The A1 reference changes for each row, while the search column remains fixed.

Use SUMPRODUCT to check a range across multiple columns
Use this option when the value could appear in any cell within a rectangular range.
Check for matching values in WPS Spreadsheet
Use a MATCH formula in WPS Spreadsheet to check a value against a column on another sheet and return 1 or 0.
- 1. Open your workbook: Open WPS Spreadsheet and select Open to load your workbook.
- 2. Select a result cell: On the sheet containing the value, click a blank cell next to A1.
- 3. Enter the formula: Type =--ISNUMBER(MATCH(A1,Sheet1!$A:$A,0)), adjusting the sheet name and search column as needed, and press Enter.
- 4. Check other rows: Drag the result cell's fill handle down to apply the formula to the remaining values.

Frequently Asked Questions
Why does the formula use two minus signs?
ISNUMBER returns TRUE when MATCH finds a position and FALSE when it does not. The two minus signs convert those results to 1 and 0.
How do I reference a sheet with spaces in its name?
Put single quotes around the sheet name, for example: =--ISNUMBER(MATCH(A1,'Product List'!$A:$A,0)).
Can I search more than one column?
Yes. Use a bounded range with SUMPRODUCT, such as =--(SUMPRODUCT(--(Sheet1!$A$1:$C$100=A1))>0). Adjust the range to cover the cells you need.
How can I leave the result blank when the lookup cell is empty?
Wrap the formula in IF: =IF(A1="","",--ISNUMBER(MATCH(A1,Sheet1!$A:$A,0))). This returns a blank result when A1 is empty.




