How to find amendments in second excel sheet

[excel_blur]

Dear forummers

I hope you can help me. I am a very basic excel user.

If i have
Excel sheet 1, at row 30 - John, john@yahoo.com
Excel sheet 2, at row 1 - John, john_new@yahoo.com
Note that the 2 sheets are not of same length


How do i write a formula such that i can ask Excel to find John in sheet 2, and if the email address is different, highlight it.

Excel sheet 1 is my mailing list that has 4000 names
Excel sheet 2 is a list that I get from another department, using another system, that shows the email address changes, maybe only 100 changes each month.
I have to update my 4000 mailing list monthly.

Right now i put Excel sheet 1 and 2 together and then sort it and then use my eyes to scan 4000 lines for any different emails. I know i am at a very prehistoric excel level. My apologies.

I thank you for any advice.

 

[excel_blur]

Hi again,

In my 4000 members list, there is Lilly Wilson, then she updated her surname to Huntington.
My membership department gives me a list of 200 changes, and in it i can see Huntington. The email is the same.

I think i need to understand the formula a little. =ISNUMBER(MATCH(A2,Sheet2!A:A,0)).

What does A:A,0 mean please?

Because it produced a TRUE, see below.

 

[excel_blur]

Hi
I would just like to provide more background.

My Excel list of changes can be any field, because members use a sign up form with fields they can change.

I get this list from my membership dept.

Then i need to find which email has changed and then i use the email to update a maichimp database, before i import the list to mailchimp. The email is a unique identifier.

Had i simply imported the changes list to mailchimp, i will have old emails and new emails, because mailchimp just adds on new emails. Certainly, it would not delete emails from its users database.

Thanks so much!!

 

[svendiamond]

Sorry OK let me clarify a couple things...

There are two formulas used. One is MATCH. MATCH returns the row number that a lookup value exists in within a lookup range.

=MATCH(lookup_value (A2), lookup_range (Sheet2!A:A means column A in sheet 2), 0)

The zero is because that third argument for MATCH function asks for a number. Zero represents an exact match. You can google the function names to learn more. You can also click the "fx" button in Excel.

So, if the MATCH function finds that membership ID on Sheet 2 column A, it returns the row number in which it was found. If it's not found, it will give a #N/A. So basically since you don't care about the row number - just that exists on the second page at all - then you can just see if MATCH returned a number, by using ISNUMBER, which simply returns TRUE or FALSE whether the thing you put inside the brackets returns a number. So ISNUMBER("poop") would return FALSE. So would ISNUMBER(#N/A).


Anyway for the first row not being highlighted, it might be that your "applies to" range is not making sense with the formula you enter. Make sure to include row 1 if your formula started at row 1. In other words, just follow my screenshot exactly, for where I gave you the example for "applies to"

 

[excel_blur]

Hello there!

I see! i now understand things better and am able to do something.

What i did was let the match returned a position value. Then i sorted the whole sheet and ignored the hexNA (sorry my keyboard could not type a hex)

This gives me a sheet 1 to compare with sheet 2 to find the changes.

Yay. I took quite long but it is kinda fun.

 

[excel_blur]

Thank you so much from big time newbie. !!!