MENU
  1. Inicio
  2. MS Office
  3. Excel
  4. Comparar listas en Excel y marcar coincidencias con COUNTIF, XMATCH y más

Comparar listas en Excel y marcar coincidencias con COUNTIF, XMATCH y más

Excel

Cuando trabajas con bases de datos en Excel a menudo necesitas saber, de forma instantánea, si un valor de una lista corta existe dentro de otra mucho más grande. Este artículo te guía paso a paso para lograrlo con fórmulas nativas —sin usar macros— y además explora variantes avanzadas y trucos de rendimiento.

Índice

Contexto del problema

Imagina un archivo con dos hojas:

  • Hoja A – Columna A: alrededor de 6 000 códigos de producto.
  • Hoja B – Columna A: unos 300 códigos que quieres auditar.

El objetivo es que, junto a cada código de la Hoja B, aparezca “Yes” si el mismo código existe en la Hoja A y “No” en caso contrario. El reto incluye:

  1. Evitar errores humanos al filtrar o copiar–pegar manualmente.
  2. Mantener la solución actualizable si la lista grande crece o se recorta.
  3. Minimizar el impacto en el rendimiento del libro.

Método recomendado: combinación IF + COUNTIF

=IF(COUNTIF('Hoja A'!A:A, A1) > 0, "Yes", "No")

Cómo funciona:

  • COUNTIF recorre toda la columna A de la Hoja A buscando el valor de la celda A1 de la Hoja B.
  • Si encuentra al menos una coincidencia, el resultado será > 0.
  • IF evalúa ese número: devuelve “Yes” cuando es positivo y “No” cuando es 0.

Guía paso a paso

  1. Activa la Hoja B y sitúa el cursor en la celda B1.
  2. Pega la fórmula anterior.
  3. Reemplaza 'Hoja A' con el nombre exacto de tu pestaña de 6 000 filas.
  4. Pulsa Enter y arrastra el controlador de relleno hasta la fila 300.

Qué hacer si la fórmula no devuelve lo esperado

SíntomaCausa habitualSolución
Las primeras filas responden correctamente, pero a partir de cierto punto todo es “No”.Se copió la fórmula antes de actualizar la referencia de hoja o se puso en la hoja equivocada.Comprueba que la fórmula está en la Hoja B y que cada fila referencia su propia columna A (A1, A2, A3…).
El resultado ignora diferencias entre mayúsculas y minúsculas.COUNTIF es insensible a mayúsculas.Cambia a:
=IF(SUMPRODUCT(--EXACT(A1,'Hoja A'!A:A))>0,"Yes","No")
Deseas evitar fórmulas obsoletas en versiones antiguas.Tu organización usa Excel 2007‑2013.Emplea:
=IF(ISNUMBER(MATCH(A1,'Hoja A'!A:A,0)),"Yes","No")
Trabajas con Microsoft 365 y quieres la función más moderna.XMATCH ofrece más versatilidad.=IF(ISNUMBER(XMATCH(A1,'Hoja A'!A:A,0)),"Yes","No")

Comparación de métodos

FunciónSintaxisVentajasLimitaciones
COUNTIFCOUNTIF(rango, criterio)Fácil de leer; rápida para una sola condición.No distingue mayúsculas; sin búsqueda aproximada.
MATCHMATCH(valor, rango, 0)Compatible desde Excel 97; admite coincidencia exacta o aproximada.Devuelve posición numérica, requiere ISNUMBER o IFERROR.
XMATCHXMATCH(valor, rango, [match_mode])Más rápido en Excel 365; entiende búsqueda inversa y comodines.No disponible en versiones anteriores.
SUMPRODUCT + EXACTSUMPRODUCT(--EXACT(valor,rango))Case sensitive; 100 % exactitud.Leve impacto en rendimiento con rangos grandes.

Estrategias para optimizar el rendimiento

Aunque 6 000 filas no son excesivas, las siguientes pautas mantendrán tu archivo ágil cuando el listado crezca a cientos de miles:

  • Uso de rangos dinámicos acotados. Sustituye A:A por un rango explícito (A1:A6000) o por un Named Range que se expanda automáticamente con OFFSET o Table (Lista de Excel).
  • Tabla estructurada. Convierta la Hoja A en una tabla (Ctrl + T) y usa el nombre de la columna (Tabla1[Códigos]). Las tablas solo recorren filas existentes.
  • Cálculo manual. Si tu laptop se ralentiza, activa “Cálculo manual” (Alt + M + X + M) y luego presiona F9 cuando necesites refrescar.
  • Evita fórmulas volátiles como OFFSET o INDIRECT salvo que sean imprescindibles.

Aplicar formato condicional para resaltar coincidencias

Más allá del campo “Yes/No”, puedes colorear automáticamente los valores duplicados:

  1. Selecciona el rango A2:A301 de la Hoja B.
  2. Ve a Inicio › Formato condicional › Nueva regla › Usar una fórmula que determine las celdas para aplicar formato.
  3. Pega:
    =COUNTIF('Hoja A'!$A:$A, A2)>0
  4. Define un relleno verde y acepta.

Cada vez que el código exista en la lista maestra aparecerá visualmente destacado, sin necesidad de columna auxiliar.

Soluciones avanzadas: Power Query y VBA

Power Query

Para auditorías recurrentes o con múltiples archivos, Power Query (Datos › Obtener y transformar) resulta mucho más robusto:

  1. Carga ambas hojas como consultas.
  2. Usa Combinar consultas con la opción Unir (Join).
  3. Escoge “Left Outer” para mantener los 300 códigos y añadir la coincidencia desde la lista grande.
  4. Agrega una columna personalizada con if [Hoja A.Código] = null then "No" else "Yes".
  5. Cierra y carga en una nueva hoja. El proceso es actualizable con un clic.

Macro en VBA

Si tu entorno impide usar Power Query, un pequeño procedimiento VBA recorre solo las filas relevantes y escribe “Yes/No” con mucha velocidad, especialmente útil cuando son cientos de miles de registros:

Sub MarcarCoincidencias()
    Dim dict As Object, cel As Range
    Set dict = CreateObject("Scripting.Dictionary")
    ' Rellenar el diccionario con los códigos de la Hoja A
    For Each cel In Worksheets("Hoja A").Range("A1", Worksheets("Hoja A").Cells(Rows.Count, "A").End(xlUp))
        If Not dict.exists(cel.Value) Then dict.Add cel.Value, True
    Next cel
    ' Comprobar códigos en Hoja B
    For Each cel In Worksheets("Hoja B").Range("A1", Worksheets("Hoja B").Cells(Rows.Count, "A").End(xlUp))
        cel.Offset(0, 1).Value = IIf(dict.exists(cel.Value), "Yes", "No")
    Next cel
End Sub

El uso de un Scripting.Dictionary convierte las búsquedas en operaciones O(1), de modo que hasta millones de comparaciones suceden en segundos.

Preguntas frecuentes

¿Puedo comparar más de dos columnas a la vez? Sí. Con COUNTIFS estableces múltiples criterios o con XMATCH puedes concatenar columnas. ¿Cómo trato los espacios invisibles o caracteres especiales? Utiliza TRIM y CLEAN en una columna auxiliar para “normalizar” antes de comparar. ¿Qué pasa si la lista grande está en otro archivo? Se puede vincular la ruta completa en la fórmula, aunque es más estable importar los datos vía Power Query o consolidarlos primero. ¿Por qué obtengo “No” para celdas vacías? El resultado es correcto: el valor vacío de Hoja B no está en Hoja A. Filtra primero los no nulos o encapsula la fórmula dentro de =IF(A1="","",…). ¿Existe un límite de filas para COUNTIF? No en versiones modernas de Excel. Sin embargo, en hojas de 1 048 576 filas el cálculo puede tardar. Considera Power Query si manejas volúmenes así.

Conclusión

Con una simple fórmula puedes resolver la tarea de auditar listas en cuestión de minutos y, lo más importante, dejar el proceso listo para futuras actualizaciones. COUNTIF sigue siendo el aliado perfecto para trabajos puntuales, mientras que XMATCH, Power Query o VBA te ofrecen escalabilidad y precisión total en escenarios más exigentes. Sea cual sea tu elección, el principio es idéntico: convertir la comparación manual en un flujo automatizado, fiable y protegido frente a errores humanos.

Excel
Excel Listas COUNTIF XMATCH Power Query
Índice