Tjyylli

I have the following information in a cell

| John Smith 34, Manager  | 

| Jane Doe 25, Assistant  |

| Lucio 32, Web Developer |

I also have another list with a list of names

| Lucio            |

| John Smith       |

| Jane Doe         |

| Samuel L Jackson |

[edited to clarify the question]

What I want to do is parse the data in the first set of cells, comparing the names with the information in the second set of cells and out out put a 1 if they match.

How would I do this in excel 2013?

EDIT: I think many of the answers have, due to my mistake, misunderstood that lists need to match cell by cell. I just need to make sure the names in the first list are correct and that they match at least one in the second list.

From what you describe, the easiest way to do what you're asking for would be to write a VBA macro as the formula. But your question is too vague. For example you need to say if the first list contains names that are not in the second list. If the second list contains names not in the first list. Also in your example item 1 of first list matched item 1 of second list and so on for items 2 and 3. Are you just matching against the corresponding row in the second list or against the whole list? I could make assumptions but hopefully you get what I'm saying when I say that your question is too vague.

This might work for you.

If your first list is in A1:A3 and your second list is in D1:D3, then enter this in cell E1:

=IFERROR(IF(MATCH(D1,LEFT($A$1:$A$3,LEN(D1)),0)>0,1,0),0)

Use CTRL+Shift+Enter to make it an array formula. Then copy and paste down.

I checked it with a bad value in an extra cell D4=Donald and it will return 0 if there is no exact match.

EDIT:

I should probably explain that the way this formula works is that, it will take the original list A1:A3, and cut it down to the amount of characters that is in the cell we want to check it against D1.

The LEFT($A$1:$A$3,LEN(D1)) will come back with an array of 3 values:

{"John Smith";"Jane Doe 2";"Lucio 32, "}.

From this list, we are looking for John Smith and an exact match (case insensitive), and since there is only one, it returns 1.

In D2, for example, the resulting array of new values to check is:

{"John Smi";"Jane Doe";"Lucio 32"}

Since we're looking for Jane Doe, there is one match that exactly fits, and so 1 is returned.

If NameList refers to the range (e.g: $J$8:$J$10) that contains your list of names:

This formula must be array-entered:

=COUNT(FIND(NameList,A1))

To array-enter a formula, after entering
the formula into the cell or formula bar, hold down
while hitting . If you did this
correctly, Excel will place braces {...} around the formula.

Correct me if I am wrong, but the way I understand your question is that the subject text and search strings already exist in a particular order, and you would simply like to compare the two strings. i.e.

| John Smith 34, Manager  | John Smith |

| Jane Doe 25, Assistant  | Jane Doe   |

| Lucio 32, Web Developer | Lucio      |

I use the substitute() approach, to avoid overusing iferror(). You can comparing the length of the subject text ("haystack") vs the length of the haystack, substituting the search string ("needle").

= LEN(haystack) - LEN( SUBSTITUTE(haystack, needle, "") ) > 0



  // Analysis of a matching example



  - LEN("John Smith 34, Manager") = 22

  - SUBSTITUTE("John Smith 34, Manager", "John Smith", "") = " 34, Manager"

  - LEN(" 34, Manager") = 10

  - (22 - 10) > 0 = TRUE



  // Analysis of a non-matching example



  - LEN("John Smith 34, Manager") = 22

  - SUBSTITUTE("John Smith 34, Manager", "Lucio", "") = "John Smith 34, Manager"

  - LEN("John Smith 34, Manager") = 22

  - (22 - 22) > 0 = FALSE

In the example above, you would simply use =LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))>0. Also note that you can perform case insensitive comparisons via SUBSTITUTE(UPPER(haystack), UPPER(needle), "")

I prefer this approach over iferror(find(needle, haystack)>0,false), because the error handling is inherent -more of a philosophy that error handling should be done on the macro level rather than formulaic.

Additionally, if you prefer the binary output (0, 1) vs the Boolean output (TRUE, FALSE), you can wrap your function in INT() (i.e. INT(TRUE) = 1, INT(FALSE) = 0).