FÓRMULAS DE EXCEL

Igualar dos listas en Excel

Igualar dos listas en Excel

Funciones utilizadas: INDICE, COINCIDIR, VERDADERO, ESNOD

Suponiendo que disponemos de un listado de nombres con encabezado en el rango A1:A20, y de dos listados en C1:C20 y E1:E20 con algunos de los datos del rango en la columna A pero desordenados y con faltantes en las últimas filas (rango faltante en C18:c20 y rango faltante E14:E20), podemos aplicar una fórmula que nos permite completar los datos faltantes colocándolos en orden de hallado de arriba hacia abajo. La sintaxis que debemos colocar en la celda C18 y que debemos arrastrar hasta la celda C20 es la siguiente:

{=INDICE(listaoriginal;COINCIDIR(VERDADERO;ESNOD(COINCIDIR(listaoriginal;$C$2:C17;0));0))}

Donde «listaoriginal» es el nombre que debemos darle al listado original sin encabezado (rango A2:A20), y C2:C17 es el rango de datos parciales sin encabezado del primer listado incompleto. Recordar que debemos aceptar la fórmula con CTRL+SHIFT+ENTER por ser matricial, y que debemos fijar con F4 el inicio del intervalo del rango de datos parciales (celda C2).

La sintaxis para el segundo listado (rango E1:E20) y que debemos colocar en la celda E14 y arrastrar hasta la celda E20 es la siguiente:

{=INDICE(listaoriginal;COINCIDIR(VERDADERO;ESNOD(COINCIDIR(listaoriginal;$E$2:E13;0));0))}

Donde «listaoriginal» es el nombre que debemos darle al listado original sin encabezado (rango A2:A20), y E2:E13 es el rango de datos parciales sin encabezado del segundo listado incompleto. Recordar que debemos aceptar la fórmula con CTRL+SHIFT+ENTER por ser matricial, y que debemos fijar con F4 el inicio del intervalo del rango de datos parciales (celda E2).

La fórmula combina las funciones INDICE, COINCIDIR, VERDADERO y ESNOD. Si analizamos de adentro hacia afuera la fórmula, podemos ver que la segunda función CONCIDIR junto a la función ESNOD generan una matriz de VERDADEROS y FALSOS que indican los valores faltantes del rango original (donde los valores VERDADERO indican faltantes). De esta forma, con la primer función COINCIDIR, la fórmula devolverá ésta matriz como la matriz de búsqueda, obteniendo la posición (fila) del primer valor faltante que al aplicarlo junto a la función INDICE devolverá el valor faltante correspondiente. Puedes conocer más acerca de la combinación de la función INDICE y COINCIDIR en el siguiente artículo:

Esta fórmula permite comparar dos listados, obtener los datos faltantes de uno de los listados respecto del otro, y completarlos para igualarlos en el orden hallado de arriba hacia abajo, independientemente del número de valores que falten en el registro parcial.

 

Te recomiendo ver el siguiente link con más videos de Fórmulas ingeniosas de Excel AQUÍ.

Salvo aclaración, todas las fórmulas y macros de este sitio están configuradas para aplicarse sobre la celda A1. Algunas fórmulas se encuentran encerradas entre llaves {} debido a que son fórmulas matriciales. Estas llaves no deben introducirse tecleándolas, sino que se generan automáticamente al aceptar la fórmula pulsando Control+Shift+Enter al mismo tiempo. Las fórmulas de este sitio son compatibles con versiones de Microsoft Excel® 2010 o superiores.