I have two columns of names. I want to find the names that match in each
column and display a message, "MATCH," in a third column.

I have tried this formula but it is not working:

IF(A2=B2:B1000,"MATCH",0)
Ctrl+Shift+Enter for array

Can anyone help?

Thanks,
Bob

You can use the COUNTIF() function:


=IF(COUNTIF(B:B,A2)0,"match","")
--
Gary's Student


"bob" wrote:

I have two columns of names. I want to find the names that match in each
column and display a message, "MATCH," in a third column.

I have tried this formula but it is not working:

IF(A2=B2:B1000,"MATCH",0)
Ctrl+Shift+Enter for array

Can anyone help?

Thanks,
Bob

Sorry but that is not working. Perhaps i didn't explain it correctly:

Column A Column B Column C
Row 1 name1 name4 formula = "Match"
Row 2 name2 name12 formula = "No Match"
Row 3 name3 name 7 formula = "No Match"
Row 4 name4 name1 formula = "Match"
Row 5 name5 name4 formula = "No Match"

As shown, I am trying to create a formula in Column C that will display
whether the value in Column A is present anywhere in Column B. In the example
above, Rows 1 and 4 result in "Match" because name1 and name4 are present in
Column B.

Make sense?

Thanks,
Bob

"Gary''s Student" wrote:

You can use the COUNTIF() function:


=IF(COUNTIF(B:B,A2)0,"match","")
--
Gary's Student


"bob" wrote:

I have two columns of names. I want to find the names that match in each
column and display a message, "MATCH," in a third column.

I have tried this formula but it is not working:

IF(A2=B2:B1000,"MATCH",0)
Ctrl+Shift+Enter for array

Can anyone help?

Thanks,
Bob

Gary''s student's suggestion should work for you, in C2 copied down
column but if you want anoher option try this

=IF(ISNA(MATCH(A2,B:B,0)),"No ",)&"Match"


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=503671

try this..

in C1 enter the formula

=if(A1=B1,"match","no match")

drag the formula upto which A1 & B1 has data..!

is it ok?!

-via135


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=503671

Dear Bob,

You may want to try this array formula (Ctrl+Shift+Enter)

=OR(EXACT(A11,$B$11:$B$13))

Regards

Ashish Mathur


"bob" wrote:

I have two columns of names. I want to find the names that match in each
column and display a message, "MATCH," in a third column.

I have tried this formula but it is not working:

IF(A2=B2:B1000,"MATCH",0)
Ctrl+Shift+Enter for array

Can anyone help?

Thanks,
Bob