Find something that is not an exact match

Is there a formula that will find items even though they are not exact. I tried using a variation of VLookup but failed miserably.

Lets say I have these on Sheet 1

272637 27FEB600304

SECURE TRUST B1016003 BS BMCE2


And these on Sheet2

SECURE TRUST B1016003

272637 27FEB600304 BS BMFSP  (2)

I am sure there is a way but only an expert will know the answer to that.

Here’s hoping
JagwarmanAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
aikimarkConnect With a Mentor Commented:
Look at the formulas in the attached workbook
Q_28978929.xls
0
 
KoenChange and Transition ManagerCommented:
Microsoft offers an add-in for excel that can do 'fuzzy' lookups...

you can find it here
0
 
xtermieCommented:
Vlookup could work if you don't use the TRUE;FALSE option/argument at the end (which is optional)
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
KoenChange and Transition ManagerCommented:
Vlookup without true; false is dangerous when comparing strings.. you need your data to be sorted...so you depend on the classicall alphabetical sort, which with 'fuzzy' strings could result in two near equal strings to be miles apart.
0
 
aikimarkCommented:
When there is a 'match', are the strings going to be identical in their leading (left-most) characters?
0
 
JagwarmanAuthor Commented:
Hi aikimark, yes they are
0
 
aikimarkCommented:
A function could easily do this, but you seem to have your heart set on a formula.

I was thinking there might be some way to combine the Left() and Len() functions, but I don't know if the Left() function can be used that way
0
 
JagwarmanAuthor Commented:
I would be happy to use a function aikmark
0
 
aikimarkCommented:
This will return the row in the lookuprange where the left-side-match is found.  A zero value is returned
Function Q_28978929(ByVal parmText As String, parmLookupRange As Range) As Long
    Dim rng As Range
    If Len(parmText) = 0 Then
        Exit Function
    End If
    For Each rng In parmLookupRange
        If Left(rng.Value, Len(parmText)) = parmText Then
            Q_28978929 = rng.Row - parmLookupRange.Rows(1).Row + 1
            Exit Function
        End If
    Next
End Function

Open in new window

0
 
JagwarmanAuthor Commented:
Ah now you've got me aikmark. I am obviously familiar with Sub but not function. How do I get that to run? Sorry for being so dumb
0
 
aikimarkCommented:
1. put it into a module in your workbook
2. create a formula in your worksheet that references a single cell (first parameter) and a range (second parameter)
0
 
JagwarmanAuthor Commented:
I am dumber than I thought I was :-(
0
 
JagwarmanAuthor Commented:
aikimark, sorry but I have tried to work this out, reading up on google how to kick start a function but cannot work it out.
0
 
ProfessorJimJamCommented:
@Jagwarman

please open attached file. i have put the fuzzylook UDF originally written by Alan from MRExcel

you can see that in sheet1 column B the matched value is returned from Sheet2
EE.xlsm
0
 
aikimarkCommented:
@Jagwarman

1. Do you have a module in your VB project?
If not, Alt+F11 to get you into the VB environment.  Insert a new module.  Paste my function

2. What does your formula look like?
0
 
JagwarmanAuthor Commented:
aikimark I am using this formula

=RIGHT(CELL("filename",D2),LEN(CELL("filename",D2))-FIND("]",CELL("filename",D2)))
0
 
JagwarmanAuthor Commented:
also tried =IF(L1<=1,SUM(L1:M1))
0
 
aikimarkCommented:
I don't see any reference to the Q_28978929 function.
0
 
ProfessorJimJamCommented:
0
 
JagwarmanAuthor Commented:
aikimark I'm sorry I don't know how to do that
0
 
JagwarmanAuthor Commented:
ProfessorJimJam I tried that it kinda works but I have 12000 lines and so many are invalid matches.
0
 
ProfessorJimJamConnect With a Mentor Commented:
@Jagwarman

it did not work because we did not put the % of strength.  now i set that to 75% but putting .75 into the next argument of UDF.

you can increase it, but i am sure this will work.

please see attached.
EE.xlsm
0
 
JagwarmanAuthor Commented:
both of these are going to be able to help me so many thanks and thank you for sticking with me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.