IF Statement: Can anyone help?

[bob]

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]

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

[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

[daddylonglegs]

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"

[via135]

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

[Ashish Mathur]

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