Taking first 3 characters from a cell A1 and comparing it to a range. If there's a match, the three characters from A1 go to B1.

[apace]

I am trying to compare the first three characters in A1 with a range from C1 to C100. If there is a match, the first three characters would copy to B1.

 

[etaf]

=COUNTIF(C1:C100,LEFT(A1,3)&"*")
will count if the left 3 characters in A1 are the starting 3 characters in column C
if thats the match - OR is it ANY where in the text

then an IF in B1 to copy the 3 characters
=IF(COUNTIF(C1:C100,LEFT(A1,3)&"*")>0,LEFT(A1,3),"")

 

[jasonb75]

Should the first 3 characters from A1 be all that is found in column C, the first part of a longer string, or any part of a string?

For an exact match

=IFERROR(VLOOKUP(LEFT(A1,3),C1:C100,1,0),"No Match")

For the first part of a longer string in column C

=IF(COUNTIF(C1:C100,LEFT(A1,3)&"*"),A1,"No Match")

For anywhere in a string found in column C

=IF(COUNTIF(C1:C100,"*"&LEFT(A1,3)&"*"),A1,"No Match")

 

[apace]

=COUNTIF(C1:C100,LEFT(A1,3)&"*")
will count if the left 3 characters in A1 are the starting 3 characters in column C
if thats the match - OR is it ANY where in the text

then an IF in B1 to copy the 3 characters
=IF(COUNTIF(C1:C100,LEFT(A1,3)&"*")>0,LEFT(A1,3),"")

That's perfect, thank you for the help!

 

[etaf]

you are welcome