I'm trying to use excel as a compliance tool. I have one cell, where the
name is manually typed in cell M17. For this example, let's say that the
name used is Ford. I then would like to compare "Ford" to several other
values. The other values I have are Ford Motor Company, Ford Credit Corp,
etc, etc. The list can be quite long. Is there a way to check if the if the
value in M17 matches any of the values in the list? Also is it possible that
it will flag it if it only has part of the word. For example, if the name is
Ford and checking against how can I get that to flag it eventhough it only
contains part of the values that are being checked.

The way I orginially tried to accomplish this was to write a nested if, such
as if(m17=s17, "CHECK",if(m17=s18,"CHECK","")). This obviously doesn't work
very well with long lists and doesn't solve my problem of flagging me if it
only contains part of the value being checked.

Any help would be immensely appreciated.

Thanks

Dave,
Use the FIND function. Put it in the column next to your list and
reference M17. Filter on that 2nd column

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"David Kinsley" wrote:

I'm trying to use excel as a compliance tool. I have one cell, where the
name is manually typed in cell M17. For this example, let's say that the
name used is Ford. I then would like to compare "Ford" to several other
values. The other values I have are Ford Motor Company, Ford Credit Corp,
etc, etc. The list can be quite long. Is there a way to check if the if the
value in M17 matches any of the values in the list? Also is it possible that
it will flag it if it only has part of the word. For example, if the name is
Ford and checking against how can I get that to flag it eventhough it only
contains part of the values that are being checked.

The way I orginially tried to accomplish this was to write a nested if, such
as if(m17=s17, "CHECK",if(m17=s18,"CHECK","")). This obviously doesn't work
very well with long lists and doesn't solve my problem of flagging me if it
only contains part of the value being checked.

Any help would be immensely appreciated.

Thanks

Hi,

Try the following:

=IF(ISNUMBER(FIND($B$25,B21)),"check","No match")

$B$25 contanis the name string "Ford" (w/o quotes)
B21 contains Ford Motor Co. or any other name.

Now put the names from B22 onwards and just copy the formula down.

Regards,

Ashish Mathur

"David Kinsley" wrote:

I'm trying to use excel as a compliance tool. I have one cell, where the
name is manually typed in cell M17. For this example, let's say that the
name used is Ford. I then would like to compare "Ford" to several other
values. The other values I have are Ford Motor Company, Ford Credit Corp,
etc, etc. The list can be quite long. Is there a way to check if the if the
value in M17 matches any of the values in the list? Also is it possible that
it will flag it if it only has part of the word. For example, if the name is
Ford and checking against how can I get that to flag it eventhough it only
contains part of the values that are being checked.

The way I orginially tried to accomplish this was to write a nested if, such
as if(m17=s17, "CHECK",if(m17=s18,"CHECK","")). This obviously doesn't work
very well with long lists and doesn't solve my problem of flagging me if it
only contains part of the value being checked.

Any help would be immensely appreciated.

Thanks

Hi Gary,


I don't know why, but it doesn't seem to solve my problem. Here is my list
that I am trying to compare to:
ford motor company
ford motor credit company
ford company
ford copr
general motors
general motors acceptance corp

here is my formula: =find(m17,S17:s:22,1)

Now when I put ford in, it returns a 1 value. If I put ford motor in, it
returns a 1 value, but if I put ford motor credit I get a #value error. Also
if I put general in I get the same error.

I'm not sure why this isn't working, can you explain?


"Gary L Brown" wrote:

Dave,
Use the FIND function. Put it in the column next to your list and
reference M17. Filter on that 2nd column

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"David Kinsley" wrote:

I'm trying to use excel as a compliance tool. I have one cell, where the
name is manually typed in cell M17. For this example, let's say that the
name used is Ford. I then would like to compare "Ford" to several other
values. The other values I have are Ford Motor Company, Ford Credit Corp,
etc, etc. The list can be quite long. Is there a way to check if the if the
value in M17 matches any of the values in the list? Also is it possible that
it will flag it if it only has part of the word. For example, if the name is
Ford and checking against how can I get that to flag it eventhough it only
contains part of the values that are being checked.

The way I orginially tried to accomplish this was to write a nested if, such
as if(m17=s17, "CHECK",if(m17=s18,"CHECK","")). This obviously doesn't work
very well with long lists and doesn't solve my problem of flagging me if it
only contains part of the value being checked.

Any help would be immensely appreciated.

Thanks