Excel - verificacion de datos

HOLA, tengo un pequeño inconveniente con este trabajo que l fin de cuentas quiero evitarme lo laborioso de ir ingresando dato por dato, he intentado hacerlo por mi cuenta pero solo me salen errores de N/A#

tengo una base de datos con direcciones de residencias. lo que quiero es extraer por ejemplo el nombre de la colonia donde viven y asignarle un codigo de la colonia que tengo en un listado.

ejemplo

6TA CALLE, LOTE 1 COL. ARENERA ZONA 2 VN


en la lista de colonias "la arenera" es la colonia #45


entonces quisiera que en una columna a la par saliera

6TA CALLE, LOTE 1 COL. ARENERA ZONA 2 VN | 45

No hay forma de relacionar "6TA CALLE, LOTE 1 COL. ARENERA ZONA 2 VN" con "COLONIAS LA ARENEA"
Quizás sea una referencia la parte subrayada, pero que pasa con: "4 AV 10-64 ZONA 2" ¿A que colonia pertenece?
Las direcciones que tienen el texto "COL." se podría suponer que lo siguiente hasta el punto el la COLONIA, pero ¿Y el resto?
Más que una fórmula lo que necesitarías sería una macro que podría solucionar un parte para el resto se tendría que ver todas las direcciones como identificar a que colonia pertenece.

En resumen la información no esta de una forma correcta para relacionar las dos tablas y de pendiendo del volumen de datos se tarda más haciendo una Macro que a mano.

Saludos.
\\//_