LOOKUP??

[Ket]

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

 

[JulieD]

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

 

[Peo Sjoblom]

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

copy down

 

[Ket]

Thank you

 

[Ket]

Many thanks Julie.

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]

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?

 

[Peo Sjoblom]

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

 

[Aladin Akyurek]

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.