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

Workbook 2
Column A

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

  • Help us to help you.... Post an example. Dec 23, 2015 at 23:17
  • I have updated the main post. Dec 23, 2015 at 23:22
  • Which ones are meant to return YES?
    – DavidPostill
    Dec 23, 2015 at 23:52
  • I am trying to compare the two columns in different workbooks and find a match. I am working with 11 and 12 digit UPCs and want to match them up and provide a "Yes" response to good matches. Those that don't have a match should return a "No" response. Dec 23, 2015 at 23:58
  • For those of us unfamiliar with how 11 and 12 digit UPC codes "match", what is the rule? Which records in your example should get a Yes and which should get a No? Between your question, the edit, and your comment, you still have not defined what you want to accomplish in Excel.
    – fixer1234
    Dec 24, 2015 at 2:51

2 Answers 2


In Workbook1, you can put a formula in the next column, which cuts away the last char and looks it up in the other workbook:


this would give you a the found match, or an error. If you like "YES" or "NO", you can embed it into an IF:


Note that you will need to adjust Workbook2!$A$2:$A$100 to match your data in workbook 2. Then, copy that formula down for all lines.

  • Neither one of these seemed to work. I got an #N/A response for all items using the first formula. And I got all "YES" response when using the second formula. I am not sure what I may have done incorrectly. Dec 23, 2015 at 23:43

This formula must be array-entered:


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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .