Excel Vlookup

I am using Vlookup to lookup text in a text string in Column 1 on Workbook 1 against column 2 in Workbook 2 I want to return the contents of column 1 in Workbook 2, if there is a match.  Sometimes this works perfect, but the majority of the time, it doesn't what am I doing wrong.  The format is general.

Because of company policy I cannot attach the file, but if I can talk to someone that would be great, however, if the expert can give me a way to communicate with them, that would be great.

Version 2013

Example of Text Strings:

(Redacted for personal information)

Everything is formatted as General

Vlookup String (=VLOOKUP(A2,BA!$A$1:$B$36,2))
dphine00Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
The only approved method for communicating in this forum is via the comments posted to this question.

While I understand company policy prevents you from posting the actual workbook you are working on, and version with any confidential information removed would greatly assist us in providing you with a formula.

However, looking at your formula, you are omitting the 4th parameter which specifies whether to look for exact matches. Perhaps try adding FALSE or 0 and see how it goes...

    =VLOOKUP(A2, BA!$A$1:$B$36, 2, 0)

Wayne
0
dphine00Author Commented:
Hi Wayne, I have added the 0, and False, and I am getting the same results.  Let me save a version that I can share.
0
dphine00Author Commented:
I have added the file
b00k1.xlsx
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Wayne Taylor (webtubbs)Commented:
Ahh, VLOOKUP works best when it knows what it's looking for. Because the "alias" isn't always at the start, it won't work.

If the alias is always 4 characters long and is always at the start of the "Short Text", you could use this....

    =VLOOKUP(LEFT(A2, 4),BA!$A$1:$B$36,2,FALSE)

If you are unable to ensure the alias is always at the start, you will likely need a VBA solution, such as this...

Sub findname()

    Dim cell As Range, cell2 As Range
    For Each cell In Range([B2], [A2].End(xlDown).Offset(0, 1))
        With Worksheets("BA")
            For Each cell2 In .Range(.[A2], .[A2].End(xlDown))
                If InStr(cell.Offset(0, -1), cell2) Then
                    cell = cell2.Offset(0, 1)
                End If
            Next
        End With
    Next    
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dphine00Author Commented:
Thanks Wayne, I will most likely have to use the VBA solution.  I will accept this, then I will have to delete the question, since I left data in the original question that I should not have.  Thank you.
0
dphine00Author Commented:
It worked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.