LOOKUP??

Hello,

I have a set of surnames in column A and in Column B. Is there a way I
can match the first 6 characters in column A, cell A1 to the first 6
characters in the entire column B and then have cell C1 show whether
there is a match.
I then need to do the same with the contents in cell A2 and so on and
so forth.

TIA

Ket
London

=IF(LEFT(TRIM(A1),6)=LEFT(TRIM(B1),6),"Match","No Match")

copy down

--

Regards,

Peo Sjoblom

"Ket" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have a set of surnames in column A and in Column B. Is there a way I
> can match the first 6 characters in column A, cell A1 to the first 6
> characters in the entire column B and then have cell C1 show whether
> there is a match.
> I then need to do the same with the contents in cell A2 and so on and
> so forth.
>
> TIA
>
> Ket
> London

Hi Ket

=IF(COUNTIF($B$1:$B$500,LEFT(A1,6)&"*")=0,A1," ")

this will put names from column A where there is no match into column C

it is an array formula so it needs to be entered into C1 using control &
shift & enter not just enter, then fill down the rest of column C, to the
row where the data in column A finishes.

Hope this helps
Cheers
JulieD



"Ket" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have a set of surnames in column A and in Column B. Is there a way I
> can match the first 6 characters in column A, cell A1 to the first 6
> characters in the entire column B and then have cell C1 show whether
> there is a match.
> I then need to do the same with the contents in cell A2 and so on and
> so forth.
>
> TIA
>
> Ket
> London

Thank you


On Tue, 22 Feb 2005 08:55:04 -0700, "Peo Sjoblom" <[email protected]>
wrote:

>=IF(LEFT(TRIM(A1),6)=LEFT(TRIM(B1),6),"Match","No Match")
>
>copy down

Many thanks Julie.

On Tue, 22 Feb 2005 23:54:33 +0800, "JulieD"
<[email protected]> wrote:

>Hi Ket
>
>=IF(COUNTIF($B$1:$B$500,LEFT(A1,6)&"*")=0,A1," ")
>
>this will put names from column A where there is no match into column C
>
>it is an array formula so it needs to be entered into C1 using control &
>shift & enter not just enter, then fill down the rest of column C, to the
>row where the data in column A finishes.
>
>Hope this helps
>Cheers
>JulieD
>
>
>
>"Ket" <[email protected]> wrote in message
>news:[email protected]...
>> Hello,
>>
>> I have a set of surnames in column A and in Column B. Is there a way I
>> can match the first 6 characters in column A, cell A1 to the first 6
>> characters in the entire column B and then have cell C1 show whether
>> there is a match.
>> I then need to do the same with the contents in cell A2 and so on and
>> so forth.
>>
>> TIA
>>
>> Ket
>> London
>

Thanks Peo, your formula does exactly what I asked for but......I
asked the wrong question.

My understanding is that it compares A1 to B1 and returns Match or No
Match.
I need it to compare A1 to the entire column B and then return a match
if the value exists anywhere in the column.
Can you assist?

On Tue, 22 Feb 2005 08:55:04 -0700, "Peo Sjoblom" <[email protected]>
wrote:

>=IF(LEFT(TRIM(A1),6)=LEFT(TRIM(B1),6),"Match","No Match")
>
>copy down

OK, so you want to know if the entry in A (first 6 characters) match the 6
first characters for any value in B? If so

=ISNUMBER(MATCH(LEFT(TRIM(A1),6),LEFT(TRIM($B$1:$B$100),6),0))

entered with ctrl + shift & enter will return TRUE for every value in A that
has a match
when copied down



--

Regards,

Peo Sjoblom


"Ket" <[email protected]> wrote in message
news:[email protected]...
> Thanks Peo, your formula does exactly what I asked for but......I
> asked the wrong question.
>
> My understanding is that it compares A1 to B1 and returns Match or No
> Match.
> I need it to compare A1 to the entire column B and then return a match
> if the value exists anywhere in the column.
> Can you assist?
>
> On Tue, 22 Feb 2005 08:55:04 -0700, "Peo Sjoblom" <[email protected]>
> wrote:
>
> >=IF(LEFT(TRIM(A1),6)=LEFT(TRIM(B1),6),"Match","No Match")
> >
> >copy down
>

This can be set up as an odinary formula for faster calculation...

=ISNUMBER(MATCH(LEFT(A1,6)&"*",$B$1:$B$100,0))+0

with 1 = match and 0 = no match.

This should be also faster than an ordinary CountIf formula.

Peo Sjoblom wrote:
> OK, so you want to know if the entry in A (first 6 characters) match the 6
> first characters for any value in B? If so
>
> =ISNUMBER(MATCH(LEFT(TRIM(A1),6),LEFT(TRIM($B$1:$B$100),6),0))
>
> entered with ctrl + shift & enter will return TRUE for every value in A that
> has a match
> when copied down
>
>
>