# 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?
###### Who is Participating?

x
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.

Commented:
Use this formula...

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

Saurabh...

Experts Exchange Solution brought to you by

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

Author Commented:
Would you mind written this in English?  Trying to figure out how 'Smith' would work in the formula
Commented:
In the formula i have assumed your smith is written in A4 cell..

Saurabh...
Author Commented:
and the 2^15?
Commented:
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...
Author Commented:
so in D:D,1),E:E)    Column D is the source and E is the target. right?
Commented:
Yes..and the value you are looking for A4, In D Column and as a result you want E Column as answer..
Author 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?
Commented:
Their you go..I believe this solves for you..

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

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