Best formula to compare 2 fields in a multi tab Excel Workbook

[Ianteuk]

Hi All,

My first post so please be kind to me

My question is a straightforward one, however there a a few complexities I need to work out.

I am trying to do a compare on 2 cells to see if the same text exists in both, now this is my problem, the cells are on different tabs, I would also need to search through all fields in the tab to find the matching email address & show the result as true of false.

Another issue is the field I am searching in contains 3 or more email addresses, so although the 2 cells do contain the same email address, because the second cell has more address in it, it comes back as False or errors. the format is smtp:First.Last@DomainA.local+smtp:First.Last@DomainB.local+Smtp:First.Last@DomainC.co.uk I know that the email address I need to find is always the last address in the cell, regardless of how many are in the cell, dont know if this helps matters.

I cannot show you my data as the content is confidential, but I can try to replicate some cells with dummy data, but I dont know if anyone could help me without me having to do that?

 

[Roderick_E]

Hi there & welcome.
A couple of clarification questions. Let's call one tab sheet1 and the other sheet2.

Are you wanting to look at a column on sheet1 and see if the data is also on sheet2 in a specific column?
If so, on sheet1, choose a blank column at row 1, and type this:

=match("*" & a1 & "*",sheet2.range("A:A"),0)

Now, you'll need to fix my code above to fit your needs.
1) a1 will need to be changed to the column and first cell where your data is on sheet1
2) sheet2.range will need to be renamed to your exact name of your other sheet. 'This is a sample'! Do it exactly like that sample but replace "This is a sample" with the name of your sheet.
3) A:A will need to be changed to the range you want to compare on sheet2.

Once that is done, you can drag this code down as many rows as you have data. It should populate some cells with #N/A and some with numbers. The numbers are the rows numbers that the data is found on sheet2. #N/A means not found in this case. No match.

 

[Ianteuk]

Thanks so much for your reply.

I have tried your code and adapted it to match my sheet info, I get an error message.....

This is the code I have amended >>>> =MATCH("*" & D6 & "*","Office 365 Users 5th April 2018"("H6:H5000"),0)

I have also tried >>>> =MATCH("*" & D6 & "*",'Office 365 Users 5th April 2018'!H6:H5000,0) that just shows the same error.

Can you assist me further on this?

Thanks Ian

 

[Roderick_E]

The second option should work. What is the exact error you see? Thanks