# How do I match a partial string in cell against a list Excel 2013?

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.

Super User Asked by Aasim Azam on November 27, 2021

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).

Answered by jtrumbull on November 27, 2021

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.

Answered by Ron Rosenfeld on November 27, 2021

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.

Answered by LorneCash on November 27, 2021

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.

Answered by Joseph on November 27, 2021

## Related Questions

### Excel: Change default encoding (file origin) of Text Import Wizard to UTF-8 (65001 : Unicode)

8  Asked on December 22, 2020 by dizzley

### Multistep ssh – is it possible?

3  Asked on December 22, 2020 by vahagn-tumanyan

### How to flash firmware upgrade to Thinkpad Ultra Docking Station on Linux?

4  Asked on December 22, 2020 by dirdi

### Microsoft Word: Table of Figures Ordering

2  Asked on December 21, 2020 by scoopsahaagendazs

### not able to open my downloads through terminal in Mac OS

0  Asked on December 21, 2020 by daiwik-kashyap

### Windows Media Player – Missing ID3 tags on some ripped CD tracks

1  Asked on December 21, 2020 by ex-umbris

### Which is lighter, running Android Virtual Device in WSL2 or Windows?

0  Asked on December 21, 2020 by kidfrom

### What might cause powerline networking to disrupt other ethernet connections?

1  Asked on December 21, 2020

### Chrome high processor usage on Asus Tuf A15

0  Asked on December 20, 2020 by geeky-gurdeep

### Zoom meeting software: how to end the call though pressing a physical button?

2  Asked on December 20, 2020 by ryan-lyu

### Renaming disks: from D: to F:

1  Asked on December 20, 2020 by user2925716

### IF function calculates result correctly, then evaluates to #N/A

0  Asked on December 19, 2020 by aqua

### NMAP Always says host is down (when using -Pn it says ports filtered)

1  Asked on December 19, 2020 by ovxrfl0w

### Laptop suddenly reboots mid-game but not in stress-tests?

1  Asked on December 19, 2020 by an-ant

1  Asked on December 19, 2020 by guy-thomas

### Mysql Workbench import CSV 0 record imported

1  Asked on December 19, 2020 by zhenyu

### How to edit local group policy objects via command line?

4  Asked on December 19, 2020