Returning values using only 4 characters to search

[Jim Green]

Hi,

i am looking to return the value from a column C which is related to column B. the issue is that only the first 4 characters of the word match and column B cannot be sorted either.
for example i have;
column B Column C
FANCOTE ALBIAV
NA AMITBO
STOREY ANDERO
CUL-DE-SAC ARISPA
KENTUCKY ARMADR
BUFFER ARMALA
STOCKTON ARMARO
BUFFER AROMCR
KINSHIP AUBIGR
AROMATIC FANCAV

how can i get excel to identify the first 4 characters of the two columns and then return column C's whole word into column A (in this case FANCOTE will have FANCAV next to it in column A)

[Colin Legg]

Hi,

Something like this perhaps:

Please Login or Register  to view this content.

In the example provided, FANCOTE will return FANCAV and AROMCR will return AROMCR. All the others will return #N/A since there are no 4 letter matches.

[Jim Green]

Thank you Colin, thats done the trick.