Aprende a comparar dos listas en Excel y extraer los datos que se repiten y los que no. No es necesario que compares uno a uno los valores de tus tablas. Puedes utilizar una fórmula que lo haga al instante por ti. Además, te enseñamos a aplicar un formato condicional a la lista para que tus compañeros y jefes se queden boquiabiertos.

Características de las listas a comparar

Para poder comparar dos listas necesitas dos rangos de celdas en Excel que contengan los datos que quieres comparar. Normalmente estos datos están organizados en columnas ya que suelen ser listas bastante extensas en las que es realmente tedioso comparar uno a uno los valores.

Nuestras listas de ejemplo tienen dos columnas cada una. Una contiene 50 países europeos y sus capitales y la segunda contiene únicamente 31 de esos países europeos.

comparar dos listas en excel

Para comparar dos listas debes tener en cuenta que:

  • Las listas a comparar no tienen por qué estar estructuradas de la misma manera. Podríamos tener una lista con los nombres de los países y sus capitales y una segunda lista con los nombres de los países únicamente o los nombres de los países y su población, PIB o cualquier otro dato
  • Solo necesitas un campo o columna común. Pueden ser nombres, apellidos, emails, etc.
  • Los valores a encontrar deben estar escritos exactamente igual. Cualquier cambio en la escritura del valor hará que no coincida con el otro valor por lo que Excel no lo tomará como campos iguales. Debes de estar atento a los espacios en el texto, las tildes, mayúsculas, etc.
  • No es necesario que los valores estén ordenados de la misma forma. No importa que el orden de los valores a comparar sea diferente.

Comparar dos listas con la función CONTAR.SI

Vamos a utilizar la función CONTAR.SI para comparar las dos listas de Excel.

Lo primero que debes hacer es incluir una columna más en la tabla en la que quieres visualizar los cambios. En el caso del ejemplo el listado 1. El nombre del encabezado de la columna será ¿Aparece en el listado 2? Ya que es la pregunta que nos hacemos.

La función CONTAR.SI cuenta el número de celdas que cumplen el criterio que establezcas. Aprende más sobre esta función en el tutorial Función CONTAR.SI y CONTAR.SI.CONJUNTO en Excel.

En este caso queremos contar los países del listado 1 que aparecen en el listado 2. Comprobaremos una a una los países del listado 1.

En la primera celda de la columna que hemos creado introducimos la fórmula CONTAR.SI donde:

  • Rango es el rango de celdas dónde queremos buscar.
  • Criterio es el valor que queremos buscar.

Escribe en la celda =CONTAR.SI

El rango serán todos los datos del listado 2 que queremos comprobar. Colócate en la primera celda y pulsa CTRL + SHIFT + ↓ para seleccionar toda la columna de datos. En nuestro ejemplo F6:F36.

Pulsa la tecla F4 tras seleccionar el rango para fijarlo en el resto de celdas donde copiarás la función.

El criterio será el nombre del país que estás buscando. En el ejemplo Albania, selecciona la celda donde se encuentra el nombre. En el ejemplo A6.

Sintaxis de la función CONTAR.SI para el ejemplo

La función quedaría así: =CONTAR.SI($F$6:$F$36;A6)

comparar dos listas en excel

Si el valor buscado se encuentra en el Listado 2 te arrojará el valor 1 y en caso contrario te arrojará el valor 0.

Copia la función en el resto de celdas de la columna haciendo doble clic sobre la esquina inferior derecha de la celda donde has introducido la función.

Debes interpretar los valores resultantes de esta forma:

  • 1 el valor buscado sí se repite en el otro listado.
  • 0 el valor buscado no se repite en el otro listado.

Sustituir los 1 y 0 por texto personalizado.

La pregunta que habíamos formulado era ¿Aparece en el segundo listado? Y la respuesta correcta a esta pregunta debe ser un sí o un no. Vamos a cambiar los unos y ceros por un texto personalizado utilizando la función SI.

En la primera celda donde has introducido la función, coloca el cursor del ratón a continuación del símbolo igual y escribe la función SI.

Aprende todo acerca de esta función en el tutorial función SI de básico a avanzado.

La sintaxis de la función SI será:

  • Prueba lógica. Comprobar si el resultado de la función CONTAR.SI es igual a 1. Con lo que la prueba lógica quedaría así: CONTAR.SI($F$6:$F$36;A6)=1
  • Valor si verdadero. En caso de que la prueba lógica sea verdadera queremos que se muestre el texto “SÍ”. Recuerda que el texto a mostrar debe escribirse entrecomillado.
  • Valor si falso. En caso de que la prueba lógica sea falsa queremos que se muestre el texto “NO”.

Por lo que la función quedaría así: =SI(CONTAR.SI($F$6:$F$36;A6)=1;”SÍ”;”NO”)

comparar dos listas en excel

Copia la función en el resto de celdas haciendo doble clic en la esquina inferior derecha de la celda donde has introducido la función SI.

Cambiar color de las filas y del texto si se repiten los valores o no

Para dar un aspecto más profesional a tu tabla y que sea más sencillo encontrar los valores repetidos o no al comparar dos listas en Excel puedes aplicar un formato personalizado a las filas.

Si lo configuras correctamente conseguirás un formato dinámico que cambie automáticamente cuando el valor se repita en el otro listado o no.  En el tutorial cambiar color de las filas según valor te explicamos paso a paso cómo hacerlo.

A continuación, te explicamos forma rápida te explicamos cómo hacerlo:

  • Selecciona todo el rango de datos donde quieres aplicar el formato.
  • En la pestaña de Inicio, despliega donde dice Formato Condicional y elige la opción Nueva regla.
  • En la ventana emergente selecciona la última opción “Utilice una fórmula que determina las celdas para aplicar formato”.
  • En la barra de descripción de la regla escribe =$C6=”SÍ”
  • Pulsa en el botón Formato y establece el formato que quieres aplicar a las filas de datos que SÍ aparecen en el listado 2.
  • Acepta el formato y la regla introducida y comprueba que se ha aplicado el formato.
comparar dos listas en excel

Finalmente, si también quieres aplicar un formato diferente a los valores que no aparecen en el listado 2 repite la operación, pero esta vez en la regla escribe =$C6=”NO”

Te puede interesar

Si lo que necesitas es comparar características ligadas a un listado, como por ejemplo los precios de dos listados de productos o las tallas disponibles de dos listados de productos, te explicamos cómo hacerlo en este enlace.

Plantilla del ejercicio

Esta plantilla es un recurso creado por Saber Programas y su uso está sujeto a derecho de autor y propiedad intelectual.




Ayúdanos a seguir creando contenido gratuito con una donación