Compare / Match records in 2 lists

[Benson112]

Hi All,

I have 2 huge lists of people who can send emails and people who can only receive emails.

I need to match up the people who can do both, is there any formula / lookup that could do this for me to save me going through each list manually

Thanks

 

[DonkeyOte]

assuming list 1 on sheet1 in column A and list on sheet2 column A

in B1 of sheet1

=IF(ISERROR(MATCH(A1,Sheet2!A:A,0)),"Can't do Both","Can do Both")

copy for all rows in A on Sheet1

revise your ranges accordingly

 

[Benson112]

Hi there, thanks for the reply this seemed to work brilliantly.

However, i have noticed that when i copy it into the first row the formula is correct, when i then copy it to the next row...it changes the range:

Row 1 - =IF(ISERROR(MATCH(A3:A87,Sheet2!A3:A143,0)),"Receive Only","Send and Receive")

Row 2 - =IF(ISERROR(MATCH(A4:A88,Sheet2!A4:A144,0)),"Receive Only","Send and Receive")

How do i stop it doing this as it needs to keep teh ranges the same? I have tried to name the ranges and use these names in quotes but the results are wrong now

 

[DonkeyOte]

You should have a read up on ABSOLUTE / RELATIVE references in Excel help -- type "REFERENCE" in the Index, click "About cell and range references" and then on resulting page review section "Difference between relative and absolute references" -- trust me that it will make a big difference in terms of how you use XL going forward.

For the purposes of your query, use the below:

=IF(ISERROR(MATCH($A$3:$A$87,Sheet2!$A$3:$A$143,0)),"Receive Only","Send and Receive")

 

[Benson112]

You should have a read up on ABSOLUTE / RELATIVE references in Excel help -- type "REFERENCE" in the Index, click "About cell and range references" and then on resulting page review section "Difference between relative and absolute references" -- trust me that it will make a big difference in terms of how you use XL going forward.

For the purposes of your query, use the below:

=IF(ISERROR(MATCH($A$3:$A$87,Sheet2!$A$3:$A$143,0)),"Receive Only","Send and Receive")

Thanks very much mate, will read up on absolute and relative references.