Compare / Match records in 2 lists

Benson112

New Member
Joined
Mar 20, 2008
Messages
35
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top