Function Problems

How to Use an Excel Formula to Match Values Between Two Sheets

Maira Mehtab Sep 21, 2026 868 views

Question details

Find a formula that searches for a value from one sheet in a specific column of a second sheet, returning TRUE if it exists, while allowing the formula to be copied down correctly.

Product: Excel 2016
Device & OS: not provided
Scenario: Comparing data sets across multiple worksheets to identify matching entries in a column.
Observed behavior: The user needs a dynamic Boolean formula (TRUE/FALSE) that correctly updates row references when dragged down a column, rather than keeping the lookup value static.

Before you start

Ensure both worksheets are located in the same workbook, and identify the exact range of cells on the second sheet that contains the reference data you want to match against.

Solution 1Recommended

Use the ISNUMBER and MATCH Functions

Combine the ISNUMBER and MATCH functions to verify if a value exists in another sheet and generate a TRUE or FALSE result.

The MATCH function searches for a specified item in a range of cells and returns its relative position. When wrapped in the ISNUMBER function, it converts the numerical position into a TRUE value if a match is found, or FALSE if the MATCH function returns an error (meaning no match).

To ensure the formula works when copied down a column, it is critical to use a relative reference for the lookup value and an absolute reference for the target search array.

Select the output cell

Click on the first cell in the column where you want the TRUE/FALSE result to appear (for example, next to your first data point on Sheet1).

Enter the formula

Type the formula =ISNUMBER(MATCH(A6,Sheet2!$A$1:$A$10,0)) into the formula bar. Replace A6 with your specific lookup cell, and Sheet2!$A$1:$A$10 with the exact range on your second sheet.

Apply the formula

Press Enter to see the result. It will display TRUE if the value in A6 exists in the specified range on Sheet2, and FALSE if it does not.

Copy the formula down

Click and hold the small square fill handle at the bottom-right corner of the cell, then drag it down the column to apply the formula to the remaining rows.

Absolute vs. Relative References: Make sure to use a relative reference for the lookup value (e.g., A6 instead of $A$6) so the row number updates automatically as you copy it down. However, always use absolute references (with $ signs, like $A$1:$A$10) for the search range so the target array stays fixed.

Advanced Spreadsheet Formulas

Match Data Across Sheets Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical and lookup formulas like ISNUMBER and MATCH, allowing you to quickly compare massive datasets across multiple worksheets without lag.

1. Open your workbook: Launch WPS Spreadsheet and open the workbook containing the two sheets you want to compare.
2. Navigate to the result cell: Select the cell where you want to output the TRUE/FALSE match result.
3. Input the match formula: Type =ISNUMBER(MATCH(A6,Sheet2!$A$1:$A$10,0)) and press the Enter key.
4. Fill the column: Drag the fill handle down to copy the formula across all the required rows to automatically check the rest of your data.

Fully compatible with Microsoft Excel (.xlsx) file formats and formula syntax.Lightweight and fast, ensuring smooth performance even when processing large datasets.Free and easy to use, featuring a familiar tabbed interface for managing multiple sheets efficiently.

microsoft office alternative - wps office

Frequently Asked Questions

How can I return a specific word instead of TRUE or FALSE?

You can wrap the MATCH function in an IF statement. For example, use the formula =IF(ISNUMBER(MATCH(A6,Sheet2!$A$1:$A$10,0)), "Found", "Not Found") to display custom text based on the match result.

Why does my formula return the same result for every row when I copy it down?

This usually happens if you accidentally lock the row reference of your lookup value by using an absolute reference (like $A$6). Ensure the lookup cell reference does not have a dollar sign before the row number (use A6 instead) so it changes to A7, A8, and so on as you drag it down.

How do I highlight the matched cells automatically?

You can use Conditional Formatting. Select the range you want to highlight, go to the Home tab, click Conditional Formatting > New Rule, choose 'Use a formula to determine which cells to format', and enter your =ISNUMBER(MATCH(...)) formula. Then, pick a fill color to highlight the matches.

Excel & Data Problems