¿Cómo comprobar si una celda contiene uno de varios valores en Excel?
En muchos escenarios empresariales, analíticos o de revisión de datos, es posible que tengas una lista de cadenas de texto en la columna A y desees comprobar si cada celda contiene alguno de los valores especificados, por ejemplo, los indicados en el rango D2:D7. Por ejemplo, en datos de encuestas, registros o listas de productos, es fundamental identificar si los registros incluyen palabras clave relevantes, códigos de producto o términos prohibidos. Si una celda contiene alguno de los elementos de tu lista especificada, puedes hacer que Excel devuelva True; si no, False, como se muestra en la captura de pantalla de abajo. En este artículo se explican métodos prácticos para verificar si una celda contiene uno de varios valores presentes en otro rango, ofreciendo varias alternativas para diferentes versiones de Excel y necesidades de los usuarios.
Verifica si una celda contiene uno de varios valores de una lista mediante fórmulas
Para determinar si una celda contiene alguno de los valores de texto de otro rango, puedes utilizar una fórmula de matriz que funciona eficazmente incluso con grandes conjuntos de datos. Este método es especialmente útil cuando necesitas resultados como valores lógicos (Sí/No) para usarlos en otras fórmulas, filtros o pruebas lógicas. Esta técnica funciona en la mayoría de las versiones modernas de Excel.
Escribe la siguiente fórmula en una celda vacía (por ejemplo, en la celda B2, junto a tus datos originales), y luego arrastra el controlador de relleno hacia abajo para aplicarla a las demás celdas según sea necesario. Si la celda contiene alguno de los valores de texto en tu rango especificado, devuelve Sí; de lo contrario, devuelve No. Consulta la captura de pantalla:
Consejos y notas:
- Esta fórmula realiza una comprobación sin distinguir mayúsculas y minúsculas. Si necesitas distinguir mayúsculas y minúsculas, considera utilizar una columna auxiliar o combinar fórmulas de manera más avanzada.
- Para adaptar el resultado a “Sí” o “No” en vez de Sí/No, utiliza la siguiente fórmula ajustada:
- D2:D7 es tu rango de valores (la “lista”); A2 es la celda que se va a comprobar.
- Ten cuidado con las celdas vacías o los datos que no sean texto, ya que la función SEARCH requiere una entrada válida de texto y los valores en blanco pueden provocar resultados inesperados (“Sí”).
Mostrar las coincidencias si la celda contiene uno de varios valores de una lista mediante fórmulas
En ocasiones, puede ser más útil mostrar cuáles de los valores de la lista aparecen realmente en cada celda, en vez de únicamente mostrar un resultado Sí/No. Por ejemplo, al escanear descripciones de productos o comentarios en busca de palabras clave específicas, podrías querer devolver todos los valores encontrados para analizarlos o incluirlos en informes. Puedes usar la siguiente fórmula para mostrar todos los valores coincidentes separados por comas, como se ilustra aquí:
Introduce esta fórmula en una celda vacía (por ejemplo, B2), y se enumerarán todos los valores de D2:D7 encontrados en A2, separados por comas:
Nota: D2:D7 es el conjunto de valores a buscar y A2 es la celda donde realizar la búsqueda.
Después de escribir la fórmula, pulsa Ctrl + Shift + Enter. Luego, puedes arrastrar el controlador de relleno hacia abajo para aplicar la fórmula en las demás filas, tal como se muestra en el resultado de la captura de pantalla:
- La función TEXTJOIN está disponible solo en Excel2019 y Office365. En versiones anteriores de Excel, utiliza la siguiente fórmula de matriz, introdúcela en una celda vacía y pulsa Ctrl + Shift + Enter:
Arrastra la fórmula hacia la derecha para cubrir tantas columnas como sea necesario y capturar todas las coincidencias posibles, y luego hacia abajo para cada fila. Las columnas adicionales permanecerán en blanco donde haya menos coincidencias. Este formato es especialmente útil cuando necesitas que las coincidencias aparezcan en columnas separadas:
Si tienes errores, revisa bien los rangos, asegúrate de que el área D2:D7 es correcta y confirma que estás usando el delimitador apropiado para tu configuración regional (coma o punto y coma).
Resalta las coincidencias si la celda contiene uno de varios valores de una lista con una función práctica
Si necesitas resaltar visualmente las palabras clave o frases que coincidan con cualquier valor de tu lista dentro de cada celda, esto puede ayudarte a hacer que los datos importantes destaquen para su revisión o acciones posteriores. La función Marcar palabras clave de Kutools para Excel está diseñada para este escenario, permitiéndote resaltar rápidamente cualquier instancia de tus palabras especificadas en el Área de datos sin escribir fórmulas ni utilizar código VBA. En hojas grandes o conjuntos de datos complejos, esta función resulta especialmente útil cuando la inspección manual no es práctica.
Después de instalar Kutools para Excel, sigue estos pasos:
1. Ve a Kutools > Texto > Marcar palabras clave para abrir el cuadro de diálogo, como se muestra:
2. En el cuadro de diálogo Marcar palabras clave, realiza lo siguiente:
- Selecciona tu rango de datos objetivo en el cuadro Rango.
- Elige, o introduce manualmente, las palabras clave en el cuadro Palabra clave (separadas por comas).
- Especifica un color para resaltar usando la opción Color de fuente de palabra clave.
3. Haz clic en OK. Todas las palabras coincidentes dentro del rango seleccionado serán resaltadas con el color de fuente elegido:
- Esta función modifica directamente el formato de visualización de las palabras clave encontradas, facilitando la revisión o el intercambio de resultados de forma más intuitiva para los equipos que no entienden las fórmulas.
- Es especialmente eficaz cuando la lista de palabras clave o el rango de texto es muy grande o necesitas resaltar varios criterios a la vez.
- Verifica siempre el rango seleccionado y las palabras clave antes de confirmar, para evitar resaltar datos por error.
Comprueba si una celda contiene uno de varios valores usando formato condicional
El formato condicional estándar de Excel es otra opción eficaz para resaltar las celdas que contienen cualquier valor de tu lista especificada. Esta solución es útil para identificar rápidamente las filas relevantes visualmente, especialmente durante revisiones de datos, comprobaciones de errores o tareas de cumplimiento. A diferencia de la función Marcar palabras clave de Kutools, este método no requiere complementos, por lo que está disponible en instalaciones estándar de Excel.
Así puedes utilizar el formato condicional con una fórmula:
- Selecciona las celdas de tu Área de datos (por ejemplo, A2:A20) que quieras rastrear.
- Ve a la pestaña Inicio, haz clic en Formato condicional > Nueva regla.
- En el cuadro de diálogo Nueva regla de formato, selecciona Usar una fórmula para determinar qué celdas se van a formatear.
- Introduce esta fórmula, suponiendo que D2:D7 contiene tus valores y A2 es la primera celda de datos:
=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$7,A2)))>0- Haz clic en Formato, elige el formato deseado (por ejemplo, color de relleno) y pulsa OK.
Todas las celdas que contengan algún elemento de la lista D2:D7 se resaltarán automáticamente.
- Este método es dinámico: si actualizas el rango D2:D7, el formato se ajustará automáticamente.
- El formato condicional solo modifica la visualización: marca las celdas visualmente, pero no devuelve resultados en una columna aparte ni permite cálculos adicionales.
- El formato condicional basado en fórmula es potente, aunque si el conjunto de datos es muy grande, el rendimiento puede disminuir debido al cálculo repetido.
- Ten en cuenta que SEARCH no distingue mayúsculas y minúsculas. Para hacerlo sensible a mayúsculas y minúsculas, se necesitan técnicas avanzadas o columnas auxiliares.
Más artículos relacionados:
- Cómo comparar dos o más cadenas de texto en Excel
- Si deseas comparar dos o más cadenas de texto en una hoja de cálculo, ya sea distinguiendo mayúsculas y minúsculas o no, como se muestra en la captura de pantalla, en este artículo te explicaré varias fórmulas útiles para realizar esta tarea en Excel.
- Mostrar el contenido si la celda contiene texto en Excel
- Si tienes una lista de cadenas de texto en la columna A y una fila de palabras clave, ahora necesitas comprobar si las palabras clave aparecen en la cadena de texto. Si las palabras clave aparecen en la celda, muéstralas; si no, deja la celda en blanco, como se muestra en la captura de pantalla.
- Contar cuántas palabras clave contiene la celda según una lista
- Si deseas contar el número de palabras clave que aparecen en una celda a partir de una lista de celdas, la combinación de las funciones SUMPRODUCT, ISNUMBER y SEARCH puede ayudarte a solucionar este problema en Excel.
- Buscar y reemplazar varios valores a la vez en Excel
- Normalmente, la función Buscar y Reemplazar permite encontrar un texto específico y sustituirlo por otro, pero a veces necesitas buscar y reemplazar varios valores al mismo tiempo. Por ejemplo, reemplazar todo el texto "Excel" por "Excel2019", "Outlook" por "Outlook2019", etc., como se muestra en la captura de pantalla de abajo. En este artículo te presento una fórmula para resolver esta tarea en Excel.
Las mejores herramientas de productividad para Office
Mejora tu dominio de Excel con Kutools para Excel y experimenta una eficiencia sin precedentes. Kutools para Excel ofrece más de300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haz clic aquí para obtener la función que más necesitas...
Office Tab incorpora la interfaz de pestañas en Office y facilita mucho tu trabajo
- Habilita la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abre y crea varios documentos en nuevas pestañas de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumenta tu productividad en un50% y reduce cientos de clics de ratón cada día!
Todos los complementos de Kutools. Un solo instalador
El paquete Kutools for Office agrupa complementos para Excel, Word, Outlook y PowerPoint junto con Office Tab Pro, ideal para equipos que trabajan en varias aplicaciones de Office.
- Suite todo en uno: complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un solo instalador, una licencia: configuración en minutos (compatible con MSI)
- Mejor juntos: productividad optimizada en todas las aplicaciones de Office
- Prueba completa de30 días: sin registro ni tarjeta de crédito
- La mejor relación calidad-precio: ahorra en comparación con la compra individual de complementos