I am using Microsoft Office 2010. I have 5 worksheets.

Worksheet 1: Provided Worksheet 2: WL Tool Summary Worksheet 3: Salesforce Worksheet 4: Pipeline Worksheet 5: Combined

Typically, I get a list of 20+ names. I copy and paste those 20+ names in the provided tab.

Then I pull names from WL Tool Summary. I copy and paste 30+ names into that worksheet.

Same thing with Salesforce/Pipline.

Then I change the color of each text in each worksheet: so provided could be blue, WL tool could be green etc. I copy all the tabs into combined and I look for matches.

I know there is a way to have excel compare these values for me and spit out the result in the "Combined" tab. I tried to write the formula myself but it didn't work


Any help would be appreciated

  • Please provide some sample data so we can better help you. – Kevin Anthony Oppegaard Rose Jun 6 at 16:37
  • Ok Worksheet 1: Google, Apple, IBM, Alibaba Worksheet 2: IBM, Walmart, Target, Sony Worksheet 3: Alibaba, Netflix, AT&T, IBM Worksheet 4: Apple, Google, Natures Bounty, Miller Coors. Worksheet 5: I would like the formula to just say Match = Google, IBM, Apple etc. Ideally, it would be better if the formula said Google match found on Worksheet 4 etc. – User 1984 Jun 6 at 17:51

i would recommend start naming the ranges of data in each sheet, for example List1, List2, List3 and List4. Once you have done it, You can use following formula in worksheet5:

=A2&" Match Found in"&IFERROR(IF(MATCH(A2,List1,0)>0," Worksheet1 ",""),"")&IFERROR(IF(MATCH(A2,List2,0)>0," Worksheet2 ",""),"")&IFERROR(IF(MATCH(A2,List3,0)>0," Worksheet3 ",""),"")&IFERROR(IF(MATCH(A2,List4,0)>0," Worksheet4 ",""),"")

replace A2 with cell in which you have the name of the company you want to compare. And you can replace Worksheet1,Worksheet2,Worksheet3,Worksheet4 with the text/name you want to display.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.