SORRY FOR LONG FORMULA, BUT INSTANT
=INDEX(Database!$A$2:$A$23,IF(MAX(IFERROR(MATCH(TRIM(MID(SUBSTITUTE(A2,"
",REPT("
",LEN(A2))),((ROW($1:$4)-1)*LEN(A2))+1,LEN(A2))),Database!$A$2:$A$23,0),"-"))=0,NA(),MAX(IFERROR(MATCH(TRIM(MID(SUBSTITUTE(A2,"
",REPT("
",LEN(A2))),((ROW($1:$4)-1)*LEN(A2))+1,LEN(
This only finds the first character. That's not what is needed. The
function needs to check the availability of any of the nth word in the
sentence. So in the example, ANNA GRATTON LTD should have returned GRATTON.
Your formula only looks up the first word.
On Thu, Feb 7, 2013 at 7:18 PM, vijay
HI Santhuash,
=IFERROR(IF(FIND(" ",A2)-1 > 1,VLOOKUP(LEFT(A2,FIND("
",A2)-1)&"*",Database!A:A,1,0),""),"")
Try this. hope it helps you, Thanks
On Wed, Feb 6, 2013 at 7:39 PM, Santhosh Kumar M K wrote:
> Hi Friends,
>
> Need a help to create macro in a "Data" tab where it can recongnize a word
Hi,
Instead of Macro, you can try using match or search function. ,
On Wed, Feb 6, 2013 at 7:39 PM, Santhosh Kumar M K wrote:
> Hi Friends,
>
> Need a help to create macro in a "Data" tab where it can recongnize a word
> either by first/middle/last name if not with full name.
>
> for ex - "SHANG
Use this function
Function WLOOKUP(strText As String, rng As Range) As String
Dim lng As Long, lngI As Long
On Error Resume Next
For lng = LBound(Split(strText, " ")) To UBound(Split(strText, " "))
lngI = Application.Match(Split(strText, " ")(lng), rng, 0)
If lngI <> 0
