PARTIAL TEXT MATCH SEARCHING FOR THE FIRST 6 CHARACTERS?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KLZA - 23 Oct 2007 16:34 GMT

Hi. I have two sheets I need to find matches from sheet1 against
sheet2 looking for the first 6 characters only.

example:

sheet1            sheet2        MATCH RESULTS
ABCCOM            ABCCOM        ABCCOM
ABCINCORPORATED    XYZCOMPANY    XYZCOMPANY
XYZCOMPANY        MISCOMPANY
XYZINCORP        XXXCOMPANY

can anyone help?

Reply to this Message

eliano - 23 Oct 2007 17:46 GMT

Hi KLZA
Try:

ABCCOM    ABCCOM    ABCCOM
ABCINCORPORATED    XYZCOMPANY    XYZCOMPANY
XYZCOMPANY    MISCOMPANY
XYZINCORP    XXXCOMPANY

In XL2000 italian    Formula in C2:C5
=SE(VAL.ERRORE(CERCA.VERT(SINISTRA(B2;6)&"*";$A$2:$A$5;1;FALSO));"";CERCA.VERT(SINISTRA(B2;6)&"*";$A$2:$A$5;1;FALSO))

In English; please ceck the functions translation
=IF(ISERROR(VLOOKUP(LEFT(B2,6)&"*",$A$2:$A$5,1,FALSE)),"",VLOOKUP(LEFT(B2,6)&"*",$A$2:$A$5,1,FALSE))"

regards
Eliano

> Hi. I have two sheets I need to find matches from sheet1 against
> sheet2 looking for the first 6 characters only.
[quoted text clipped - 8 lines]
>
> can anyone help?

Reply to this Message