ExcelBanter - Compare two worksheets with "contains" and not "exact" values

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Compare two worksheets with "contains" and not "exact" values (https://www.excelbanter.com/excel-worksheet-functions/244513-compare-two-worksheets-contains-not-exact-values.html)

Compare two worksheets with "contains" and not "exact" values

Dear experts!

I have two worksheets. Given examples below:

Excel 1

Column A
00123466799
126565897854
45457878451
45546544782
155467846564

Excel 2

Column A
1234667
65897854
57878451
46544782
67846564

I want to compare both the excels...... and give me result like a "vlookup"
function... however, in this case, the columns wont exactly match - but excel
1 "contains" the values of excel 2 in the same order - ignoring prefixes or
suffixes

Compare two worksheets with "contains" and not "exact" values

In Sheet2 B1 try the below formula and copy down which will lookup and get
the value from Sheet1

=IF(A1<"",INDEX(Sheet1!A:A,MATCH("*" & A1 & "*",Sheet1!A:A,0)),"")

If this post helps click Yes
---------------
Jacob Skaria

"Fuzzy" wrote:

Dear experts!

I have two worksheets. Given examples below:

Excel 1

Column A
00123466799
126565897854
45457878451
45546544782
155467846564

Excel 2

Column A
1234667
65897854
57878451
46544782
67846564

I want to compare both the excels...... and give me result like a "vlookup"
function... however, in this case, the columns wont exactly match - but excel
1 "contains" the values of excel 2 in the same order - ignoring prefixes or
suffixes