I am trying to compare two sets of numbers but they have different formats (the length of one number is different in each list) but parts of them match. I want to find the matches and return a YES response and the ones that have no match return a NO response in a new column. Since the number format is different, VLOOKUP isn't an option.

The numbers I am comparing are on different sheets in the same workbook.

I am fairly new at Excel and am trying all kinds of ways but haven't found one that does the trick yet. I should also add that the main number I am comparing TO is in Workbook 1 as it contains more information. I am pulling it to the workbook 2 where the Yes/No response will be recorded.

Here is an example:

```
Workbook 1
Column A
1234567890
1234512345
1234561234
2345789000
Workbook 2
Column A
123456789
123451234
3456123
345678901
```

Here's what it would look like:

```
Workbook 2
Column A Column B
123456789 Yes
123451234 Yes
3456123 Yes
345678901 No
```

Any help would be greatly appreciated!

*Note*
One options I've been trying to use is =IF(ISNUMBER(SEARCH(B3,'Workbook 1'!$A$1:$A$100)), "Yes", "No")

Will this work for what I need to do? It seems that it is finicky...

Post an example.`YES`

?4more comments