Formula to check data in one sheet and show TRUE matches in second sheet

[Zyphon]

Hi All,

I have a list of companies that the company I work for use. These are all listed on a provider spreadsheet.

However recently I was given an updated list with even more providers on the list.

On top of this I have been adding providers to the list as and when I have been told to do so.

I copied this worksheet to my original workbook but as the list contains over 500 rows of provider details, I was wondering if there was a formula I could add to the updated list, a True or False column that would look at the data on sheet 2 and show true for a match and false if the comany doesn't exist.

So sheet 1 would contain the updated list that was given to me by my manager and sheet 2 is my original list. In my sheet if I could add a formula that would check sheet 1's A2:A500 and report 'True' if the provider exists. Then I could filter the False rows in sheet 2 and move them to Sheet 1.

Thanks in advance for any help offered.

 

[njimack]

Re: Formula to see data in one sheet and highlight matches in second sheet

Welcome to the Board! Assuming the data in sheet2 is also in column A, enter this into B2 of sheet 2 and copy down

=COUNTIF(Sheet1!$A$2:$A$500,A2)>0

 

[Zyphon]

Re: Formula to see data in one sheet and highlight matches in second sheet

Welcome to the Board! Assuming the data in sheet2 is also in column A, enter this into B2 of sheet 2 and copy down

=COUNTIF(Sheet1!$A$2:$A$500,A2)>0

Hi Neil,

Thanks for the welcome. Wow! What a lightning fast response. Thank you so much this is exactly what I was after, I am so pleased it is not an overly complicated formula this is small and neat.

Thanks again, really appreciate it.