Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Comparing Values in Dissimilar Spreadsheets to find and identify matches

Copper Contributor

Please can someone assist. I have tried all sorts of variations to identify matching values (Text and Numeric) in two separate worksheets. The purpose being to identify missing entries. The sheets contain different numbers of entries but DO share a common set of data in one column. I have tried to use =ISTEXT(MATCH(B3, Sheet2!$C$2:$C$33000, 0)) for example and this will not work. I have used ISNUMBER as well to ne effect. I would like to show all instances of a match to enable ALL Non matched entries to be included. Any help with this issue would be greatly appreciated


2 Replies


Would it be OK to highlight entries that do not have a match? If so:

Select column B. I'll assume that B1 is the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=ISERROR(MATCH(B1, Sheet2!C:C, 0))

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.


You can also try

=COUNTIF(Sheet2!C:C, B1)=0

as formula

Hi Hans.

That has sorted it!

Thank you for your help
Kind Regards