Function Problems

Return 1 or 0 When a Value Exists on Another Excel Sheet

Aamir Naveed AkramAamir Naveed Akram Nov 18, 2026 868 views

Question details

Check whether a value appears on another worksheet and return 1 if it does or 0 if it does not.

Return 1 or 0 When a Value Exists on Another Excel Sheet
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.
Before you start

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.

Solution 1Recommended

Use MATCH to check a column on another sheet

Use this formula when the values to search are in one column.

1
Enter the formula

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.

2
Check the result

Press Enter. The formula returns 1 if MATCH finds the value and 0 if it does not.

3
Apply it to other rows

Drag the result cell's fill handle down the column. The A1 reference changes for each row, while the search column remains fixed.

Use MATCH to check a column on another sheet
Text matching: MATCH with 0 is not case-sensitive and treats * and ? in text lookup values as wildcards. Use the multi-column comparison below if those characters must be treated literally.
Check values across worksheets

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. 1. Open your workbook: Open WPS Spreadsheet and select Open to load your workbook.
  2. 2. Select a result cell: On the sheet containing the value, click a blank cell next to A1.
  3. 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. 4. Check other rows: Drag the result cell's fill handle down to apply the formula to the remaining values.
Check values across worksheets with a simple formulaOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

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.