VLOOKUP and Instr

Is there a way to use VLOOKUP and look for a specific instr in the destination call?

Ex: =VLOOKUP(A4,'[price getter.xlsm]Converted Data'!$D:$E,2,0)

But only finding the string 'Smith' in the destination cell.    

Without having to parse or change the source cell    Any Ideas?
RWayneHAsked:
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.

Saurabh Singh TeotiaCommented:
Use this formula...

=LOOKUP(2^15,SEARCH(A4,D:D,1),E:E)

Open in new window


Saurabh...
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
RWayneHAuthor Commented:
Would you mind written this in English?  Trying to figure out how 'Smith' would work in the formula
0
Saurabh Singh TeotiaCommented:
In the formula i have assumed your smith is written in A4 cell..

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

RWayneHAuthor Commented:
and the 2^15?
0
Saurabh Singh TeotiaCommented:
That is to search entire contents of the cell as 2^15=32768 and excel has limitation around the same number of characters which you can right in a single cell..

Saurabh...
0
RWayneHAuthor Commented:
so in D:D,1),E:E)    Column D is the source and E is the target. right?
0
Saurabh Singh TeotiaCommented:
Yes..and the value you are looking for A4, In D Column and as a result you want E Column as answer..
0
RWayneHAuthor Commented:
Sorry I must be missing something..  returns N/A

Jerry Smith is in column D, formula in column E..   the word Smith is in A4..   ?   Do you have a sample sheet?
0
Saurabh Singh TeotiaCommented:
Their you go..I believe this solves for you..

Saurabh...
Lkup.xlsx
0
RWayneHAuthor Commented:
perhaps you can look at my example?
Book3.xlsx
0
RWayneHAuthor Commented:
even better, display the value of E in D if True
0
Saurabh Singh TeotiaCommented:
You can simply use this formula in E1..

=IF(ISNUMBER(SEARCH($A$4,D1,1)),TRUE,"")

Open in new window


Enclosed is your workbook..

Saurabh...
Book3.xlsx
0
Saurabh Singh TeotiaCommented:
And you can't do what you are trying to do..Because you are trying to create a cyclic formula in the example which you posted...
0
RWayneHAuthor Commented:
Thanks
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.