Vlookup to match if all but 1 letter matches?

tonywatsonhelp · Aug 20, 2014

Hi Everyone,

Ok so I've got an Idea

I want a Vlookup that looks at the cell F2 and if it finds a match using the formula below then puts that match in, but if it does not find a match it looks at the letters and finds a match for all but one letter? (the letters will be in the same order and there are no spaces)
So for example:
TonyHadABadDay
would match
TonyHadoBadDay
But Not
TonyAbaddayhad?
(they must not be case sensitive and It doesn't have to be vlookup, just a way to do this?)
is this possible?

can anyone tell me how to do it

Code:

=IFERROR(VLOOKUP(F2,'bet3'!AA:AB,2,0),"")

Thanks

Tony

WarPigl3t · Aug 21, 2014

I have no idea how to accomplish what you ask for with built in excel functions. Even if there is an option, it would be a very complex formula. I find it much easier to just make your own function. So I created one for you. Start by copy/pasting this code into your macros.

Code:

Function [COLOR=#0000ff]myFunction[/COLOR](v, rng)
    vTemp = Split(rng.Address(0, 0), ":")
    s1 = Left(vTemp(0), 1)
    s2 = Left(vTemp(1), 1)
    endLoops = False
    c = Len(v)
    r = Right(vTemp(0), 1) [COLOR=#008000]'loop counter until last row that isn't blank[/COLOR]
    Do Until Range(s1 & r).Value = "" Or endLoops = True
        mtch = 0 [COLOR=#008000]'counts characters from v that match c[/COLOR]
        cnt = 1 [COLOR=#008000]'loop counter until equal to c, evaluates each character[/COLOR]
        Do Until cnt = c
            If LCase(Mid(v, cnt, 1)) = LCase(Mid(Range(s1 & r).Value, cnt, 1)) Then
                mtch = mtch + 1
            End If
            cnt = cnt + 1
        Loop
        If mtch + 1 = c - 1 Then
            [COLOR=#0000ff]myFunction[/COLOR] = Range(s2 & r).Value
            endLoops = True
        End If
        r = r + 1
    Loop
    If endLoops = False Then
        [COLOR=#0000ff]myFunction[/COLOR] = ""
    End If
End Function

Now Here is a table that will be a dataset example.

	A	B	C
1	=myFunction($A$2,$B$2:$C$5)
2	helloWorld	hellOwarld	output1
3		Helloaorld	output2
4		Hellowworld	output3
5		HelloWrrrd	output4

in Cell A1, I typed in the function name. Then I put A2 as the lookup value. Then I put B2:C5 as the lookup range. The B column will be the lookup column. The C column will be the output column. I made the references absolute by inserting dollar signs. You don't have to if you don't want to, but it's smart. The output of A1, once you have entered that formula, will be "output2".

I colored some of the text so you can note it. It's not colored in code if you didn't already know. It won't show the colors when you paste it into vba.

This was my first time creating a custom vLookUp function. Thanks for the experience.

WarPigl3t · Aug 21, 2014

Sorry I made a mistake in my last message. The code I gave you works, but it isn't doing what is should. That code starts at the top of the range, so in my example it starts looking for matches in cell B2. It then looks at B3, B4, and B5. It stops there because B6 is blank. That is what my code is saying(Do until cell is blank). That is the problem though. It is suppose to Do Until the last cell in the range you specified. So I modified 1 line of code to resemble that. I highlighted it red. Use this code instead.

Code:

Function myFunction(v, rng)
    vTemp = Split(rng.Address(0, 0), ":")
    s1 = Left(vTemp(0), 1)
    s2 = Left(vTemp(1), 1)
    endLoops = False
    c = Len(v)
    r = Right(vTemp(0), 1) 'loop counter until last row that isn't blank
    [COLOR=#ff0000]Do Until r = Right(vTemp(1), 1) Or endLoops = True[/COLOR]
        mtch = 0 'counts characters from v that match c
        cnt = 1 'loop counter until equal to c, evaluates each character
        Do Until cnt = c
            If LCase(Mid(v, cnt, 1)) = LCase(Mid(Range(s1 & r).Value, cnt, 1)) Then
                mtch = mtch + 1
            End If
            cnt = cnt + 1
        Loop
        If mtch + 1 = c - 1 Then
            myFunction = Range(s2 & r).Value
            endLoops = True
        End If
        r = r + 1
    Loop
    If endLoops = False Then
        myFunction = ""
    End If
End Function

WarPigl3t · Aug 21, 2014

You mentioned that you wanted to do a vLookUp first. You wanted it to looks at the letters and finds a match for all but one letter only after it didn't find an exact match. Well the code I gave you doesn't find a match first. It just finds the first match that either matches or has a one letter difference. If you want to check for an exact match first, i suggest you use the vlookup function in conjunction with the function i created for you. Using my example table above, I used.

Code:

=IFERROR(VLOOKUP(A2,$B$2:$C$5,2,FALSE),myFunction(A2,$B$2:$C$5))

WarPigl3t · Aug 21, 2014

In your case you would just type

Code:

[COLOR=#333333]=IFERROR(VLOOKUP(F2,'bet3'!AA:AB,2,0),myFunction(F2,'bet3'!$AA$1:$AB$99999))[/COLOR]

WarPigl3t · Aug 21, 2014

I'm sorry, Don't use that last code I gave you. It will crash your excel. It evaluates an integer and a string which it can't do. Therefore it gets stuck in an infinite loop which causes it to crash. I had to convert the range to an integer to solve this problem. Use this code.

Code:

Function myFunction(v, rng)
    vTemp = Split(rng.Address(0, 0), ":")
    s1 = Left(vTemp(0), 1)
    s2 = Left(vTemp(1), 1)
    endLoops = False
    c = Len(v)
    Dim x As Integer
    x = Right(vTemp(1), 1)
    r = Right(vTemp(0), 1) 'loop counter until last row that isn't blank
    Do Until r = x Or endLoops = True
        mtch = 0 'counts characters from v that match c
        cnt = 1 'loop counter until equal to c, evaluates each character
        Do Until cnt = c
            If LCase(Mid(v, cnt, 1)) = LCase(Mid(Range(s1 & r).Value, cnt, 1)) Then
                mtch = mtch + 1
            End If
            cnt = cnt + 1
        Loop
        If mtch + 1 = c - 1 Then
            myFunction = Range(s2 & r).Value
            endLoops = True
        End If
        r = r + 1
    Loop
    If endLoops = False Then
        myFunction = ""
    End If
End Function

WarPigl3t · Aug 21, 2014

Okay I promise this is the last revision. You should definitely use this code. The difference between this code and the previous ones I gave you is that this code allows you to enter the output range into the function. For example, when you use the VLookUp function, you type something like this...
=VLookUp(A1,B1:F5,5,FALSE)
That will lookup the 5th column in the range which is the F column. Well I added that feature to the custom vlookup code.
Just type

Code:

[COLOR=#333333]=IFERROR(VLOOKUP(F2,'bet3'!AA:AB,2,0),myFunction(F2,'bet3'!$AA$1:$AB$99999,2))[/COLOR]

I did not add a feature for you to select the entire columns as you did with the VLOOKUP function. You have to select a range as I did from 1:99999. If you select the entire columns as your range, you might crash the system. So don't enter AA:AB as the range. Enter AA1:AB99999

Heres the final revision of the code. Njoy!

Code:

Function myFunction(v, rng, colOutput)
    vTemp = Split(rng.Address(0, 0), ":")
    s1 = Left(vTemp(0), 1)
    s2 = Left(vTemp(1), 1)
    cO = colOutput - 1
    cOut = ActiveWorkbook.Worksheets(1).Columns(s1).Column
    cOut = cOut + cO
    If cOut > 26 Then
        MyColumnLetter = Chr(Int((cOut - 1) / 26) + 64) & Chr(((cOut - 1) Mod 26) + 65)
    Else
        MyColumnLetter = Chr(cOut + 64)
    End If
    endLoops = False
    c = Len(v)
    Dim x As Integer
    x = Right(vTemp(1), 1)
    r = Right(vTemp(0), 1) 'loop counter until last row that isn't blank
    Do Until r = x + 1 Or endLoops = True
        mtch = 0 'counts characters from v that match c
        cnt = 1 'loop counter until equal to c, evaluates each character
        Do Until cnt = c
            If LCase(Mid(v, cnt, 1)) = LCase(Mid(Range(s1 & r).Value, cnt, 1)) Then
                mtch = mtch + 1
            End If
            cnt = cnt + 1
        Loop
        If mtch = c - 1 Then
            myFunction = Range(MyColumnLetter & r).Value
            endLoops = True
        End If
        r = r + 1
    Loop
    If endLoops = False Then
        myFunction = ""
    End If
End Function

tonywatsonhelp · Aug 21, 2014

Hi Warpiglet,
I really do apreasiate your trying to help on this but I can't get it to work?
I don't know what I'm doing wrong?
do I have to activate something in excel to make it function?
All I get is the error #Name (I took out the iferror to see what was wrong)
any ideas? I'm very confused?

This is what I did,
I copied your latest code into a module.
I then took the formula and pasted it in from G2 down next to F2
but nothing?

what am I doing wrong?

thanks

Tony

Thanks

Tony

hippiehacker · Aug 21, 2014

if you want to go with UDF's you could try Fuzzy Vlookup http://www.mrexcel.com/forum/excel-questions/195635-fuzzy-matching-new-version-plus-explanation.html

tonywatsonhelp · Aug 21, 2014

thanks hippiehacker but I tried that yesterday and did not get the results I want,
I though this would be easy as I'm only trying to get a match if one of the letters in order is not the same so
tonytester
would match
tonytestor
as the only difference is one letter? but clearly this is a big problem as it seams to be stumping everyone,
I'm hoping warpiglet's code is good and I'm just doing something wrong?
but I'm waiting to hear back.
thanks for the advice, and all your help yesterday etc.

Tony

Vlookup to match if all but 1 letter matches?

tonywatsonhelp

Well-known Member

Excel Facts

WarPigl3t

Well-known Member

WarPigl3t

Well-known Member

WarPigl3t

Well-known Member

WarPigl3t

Well-known Member

WarPigl3t

Well-known Member

WarPigl3t

Well-known Member

tonywatsonhelp

Well-known Member

hippiehacker

Well-known Member

tonywatsonhelp

Well-known Member

Similar threads

Forum statistics

Share this page

Vlookup to match if all but 1 letter matches?

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock