Compare Colums

**Nick.123** · 05-01-2011, 11:59 AM

Hi, I have a worksheet that has two lists of MAC address’s I would like a formula for excel to compare mac address’s in column A to column B and flag up any which are not in column A

I.E

Colum A Column B In List A
00:EE:EE:EE:41 00:EE:EE:EE:12 Match Found
00:EE:EE:EE:12 00:EE:EE:EE:00 Match Not Found
00:EE:EE:EE:13 00:EE:EE:EE:13 Match Found
00:EE:EE:EE:11 00:EE:EE:EE:11 Match Found

Any Idea's as to the best way to do this

Thank You

**zbor** · 05-01-2011, 12:02 PM

try =COUNTIF(A:A, B1)>0 this will give you TRUE/FALSE

For Match foun add one more IF statement

=IF(COUNTIF(A:A, B1)>0, "Match found", "Match not found")

**arthurbr** · 05-01-2011, 12:06 PM

Not that it matters much, but you can do without the >0 condition

Please Login or Register  to view this content.

**zbor** · 05-01-2011, 12:17 PM

=true(), true()

**mahju** · 05-01-2011, 01:27 PM

Hi
If first colomn is in A5 and 2nd is in B5 then

May be this one

Please Login or Register  to view this content.

Regards

**Nick.123** · 05-02-2011, 12:21 PM

Originally Posted by zbor

try =COUNTIF(A:A, B1)>0 this will give you TRUE/FALSE

For Match foun add one more IF statement

=IF(COUNTIF(A:A, B1)>0, "Match found", "Match not found")

Is there any way to have it return N/A if there is no MAC address In List A

I.E My Setup
List A Fomula to compare List A with list B
IP Address: Device Name: Mac Address: MAC Check:
192.168.1.1 Dlink 00:11:11:111:11:11 Match Found
192.168.1.2 No Device N/A

Thanks