If match?

[hhebe]

I need help....

I need a formula/function that will compare the data in two different columns and will return MATCH (or something similiar) if the data matches

Thanks ahead of time

 

[Excelestial]

Comparing data in Col A to data in Col B:

=If(isna(vlookup(B1,$A2:$A$2000,1,FALSE)),"","Match")

 

[hhebe]

Comparing data in Col A to data in Col B:

=If(isna(vlookup(B1,$A2:$A$2000,1,FALSE)),"","Match")

As usual Excelestial, thank you!

 

[hhebe]

Would you mind explaining the formula to me so it would help me to remember it in the future?

 

[Matty]

Another approach:

=IF(COUNTIF(A$2:A$2000,B2),"Match","")

Matty

 

[Scott Huish]

You don't need VLOOKUP because you're not returning a value and you can also check to see if it DID find it, rather than if it didn't.


=IF(ISNUMBER(MATCH(B1,$A$2:$A$2000,0)),"Match","")

 

[hhebe]

Another approach:

=IF(COUNTIF(A$2:A$2000,B2),"Match","")

Matty

Very cool, thank you Matty!

Excelestial, nevermind about the explanation, my co-worker was wondering what the 1 in the formula was but I figured out it was column number

 

[Excelestial]

HHEBE...you are entirely correct...and entirely welcome!

 

[hhebe]

Apparently none of the 3 are working correctly. Does it matter if I tell you that it will be comparing data that is already in the same row? also, the data will be 2 sets of street addresses and 2 sets of telephone numbers. Any more suggestions?

 

[snoopyhr]

Something like this perhaps ?

Excel Workbook
	A	B	C	D	E	F
2	Comp-Result	Address Col1	Phone Col1		Address Col2	Phone Col2
3		Address-1	Phone -1		Address-6	Phone -6
4		Address-2	Phone -2		Address-7	Phone -7
5		Address-3	Phone -3		Address-8	Phone -8
6		Address-4	Phone -4		Address-9	Phone -9
7		Address-5	Phone -5		Address-10	Phone -10
8	Match	Address-6	Phone -6		Address-11	Phone -11
9	Match	Address-7	Phone -7		Address-12	Phone -12
10	Match	Address-8	Phone -8		Address-13	Phone -13
11	Match	Address-9	Phone -9		Address-14	Phone -14
12	Match	Address-10	Phone -10		Address-15	Phone -15
13	Match	Address-11	Phone -11		Address-23	Phone -23
14	Match	Address-12	Phone -12		Address-24	Phone -24
15	Match	Address-13	Phone -13		Address-25	Phone -25
16	Match	Address-14	Phone -14		Address-26	Phone -26
17	Match	Address-15	Phone -15
18		Address-16	Phone -16
19		Address-17	Phone -17
20		Address-18	Phone -18
21		Address-19	Phone -19
22		Address-20	Phone -20
23		Address-21	Phone -21
24		Address-22	Phone -22
25	Match	Address-23	Phone -23
26	Match	Address-24	Phone -24
27	Match	Address-25	Phone -25
28	Match	Address-26	Phone -26
Sheet5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3	1. / Formula is =ISNUMBER(MATCH(B3&C3,$E$3:$E$16&$F$3:$F$16,0))	Abc