matching one value in a list of values

[David Kinsley]

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

 

[Gary L Brown]

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

HTH,

 

[Ashish Mathur]

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]

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?