Formula Errors

How to Use an Excel IF Formula to Check Values Across Worksheets

Tauseeq MagsiTauseeq Magsi Nov 2, 2026 869 views

Question details

The user needs to check whether a specific value from one worksheet exists in another worksheet and output a custom text result.

How to Use an Excel IF Formula to Check Values Across Worksheets
Product
Excel
Device & OS
not provided
Scenario
Comparing datasets across different worksheets to identify matching records.
Observed behavior
Needs to return 'Found' if the value exists in the target sheet and 'Not Found' if it does not.
Before you start

Ensure both worksheets are within the same workbook. Take note of your worksheet names, as any names containing spaces will require single quotation marks within the formula syntax.

Solution 1Recommended

Use IF, ISNUMBER, and MATCH Functions Together

This combination searches for a value in a target sheet and returns a custom text string based on whether an exact match is found.

The MATCH function looks for the specified value in the target sheet's column and returns its relative position.

The ISNUMBER function evaluates the MATCH result. If a position is found (meaning it is a number), it returns TRUE; if no match is found, MATCH yields an error, and ISNUMBER returns FALSE.

The IF function then translates the TRUE/FALSE logic into your desired 'Found' or 'Not Found' text.

1
Select the result cell

Click on cell C2 in SheetA (or wherever you want the result to appear).

2
Enter the formula

Type =IF(ISNUMBER(MATCH(B2,SheetB!A:A,0)),"Found","Not Found") and press Enter.

3
Handle worksheet names with spaces

If your target sheet has spaces or punctuation in its name, wrap the sheet name in single quotes. For example: =IF(ISNUMBER(MATCH(B2,'Sheet B'!A:A,0)),"Found","Not Found").

4
Copy the formula down

Select cell C2, then click and drag the fill handle (the small square at the bottom-right corner) down the column to apply the formula to the rest of your data.

Use IF, ISNUMBER, and MATCH Functions Together
Formula Breakdown: The '0' at the end of the MATCH function syntax is crucial, as it forces Excel to search for an exact match.
Free Spreadsheet Editor

Easily Check Values Across Sheets in WPS Spreadsheet

WPS Spreadsheet fully supports advanced formula combinations like IF, ISNUMBER, and MATCH. You can seamlessly compare data across multiple worksheets with a highly compatible, intuitive interface.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your datasets.
  2. 2. Input the formula: Select the destination cell and enter the identical =IF(ISNUMBER(MATCH(...))) formula.
  3. 3. Apply to column: Double-click the fill handle to automatically copy the formula down to the last row of your data.
100% compatible with Microsoft Excel formulas and .xlsx files.Syntax highlighting makes writing and editing complex nested cross-sheet formulas easier.Completely free to download and use for your daily spreadsheet tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Why is my formula returning a #N/A error instead of 'Not Found'?

If you use the MATCH function alone, it returns an #N/A error when a value isn't found. Wrapping MATCH inside ISNUMBER prevents this error by converting the #N/A into a clean FALSE value, which the IF function then uses to return 'Not Found'.

Can I use VLOOKUP instead of MATCH for this task?

Yes, you can use =IF(ISNA(VLOOKUP(B2,SheetB!A:A,1,FALSE)),"Not Found","Found"). However, combining ISNUMBER and MATCH is generally faster and more efficient for simply checking if a value exists without extracting the data.

How do I check multiple columns in the target worksheet?

The MATCH function can only search a single column or single row array (e.g., A:A). If you need to check across multiple columns simultaneously, use the COUNTIF function instead, such as =IF(COUNTIF(SheetB!A:D, B2)>0, "Found", "Not Found").

What if the text case is different in the two sheets?

The MATCH function is case-insensitive, meaning 'Apple' and 'apple' will be considered a match. If you need a strict case-sensitive search, you must use the EXACT function combined with an array formula.