Autor: Oscar Cronquist Artículo actualizado en julio 29, 2020

Este artículo muestra varias formas de verificar si una celda contiene un valor basado en una lista. El primer ejemplo muestra cómo verificar si alguno de los valores de la lista está en la celda.

Los ejemplos restantes muestran fórmulas que también devuelven los valores coincidentes. Es posible que necesite diferentes fórmulas basadas en la versión de Excel que está utilizando.

La imagen de arriba muestra una fórmula de matriz en la celda C3 que verifica si la celda B3 contiene al menos uno de los valores en la Lista (E3: E7), devuelve "Sí" si alguno de los valores se encuentra en la columna B y no devuelve nada si la celda No contiene ninguno de los valores.

Por ejemplo, la celda B3 contiene XBF que se encuentra en la celda E7. La celda B4 contiene el texto ZDS encontrado en la celda E6. La celda C5 no contiene valores en la lista.

=IF(OR(COUNTIF(B3,"*"&$E$3:$E$7&"*")), "Yes", "")

Debe ingresar esta fórmula como una fórmula de matriz si no está suscrito a Excel 365. Hay otra fórmula a continuación que no necesita ingresarse como una fórmula de matriz, sin embargo, es un poco más grande y más complicada.

  1. Escriba la fórmula en la celda C3.
  2. Mantenga presionado CTRL + MAYÚS simultáneamente.
  3. Presione Enter una vez.
  4. Suelte todas las teclas.

Excel agrega corchetes a la fórmula automáticamente si ingresó con éxito la fórmula de matriz. No ingrese los corchetes usted mismo.

Volver arriba

Explicando la fórmula en la celda C3.

Paso 1: compruebe si la celda contiene alguno de los valores de la lista

La Función COUNTIF le permite contar celdas en función de una condición, sin embargo, también le permite contar celdas en función de múltiples condiciones si usa un rango de celdas en lugar de una celda.

COUNTIFdistancia, criterios)

El argumento de criterio utiliza un asterisco inicial y final para que coincida con una cadena de texto y no con el valor completo de la celda, los asteriscos son uno de los dos caracteres comodín que puede usar.

Los símbolos de concatenación concatenan los asteriscos al rango de celdas E3: E7.

COUNTIF (B3, "*" y $ E $ 3: $ E $ 7 y "*")

se convierte en

COUNTIF ("LNU, YNO, XBF", {"* MVN *"; "* QLL *"; "* BQX *"; "* ZDS *"; "* XBF *"})

y devuelve esta matriz

{0; 0; 0; 0; 1}

que nos dice que el último valor en la lista se encuentra en la celda B3.

Paso 2: devuelve VERDADERO si al menos un valor es 1

La Función OR devuelve VERDADERO si al menos uno de los valores en la matriz es VERDADERO, el equivalente numérico a VERDADERO es 1.

O ({0; 0; 0; 0; 1})

devuelve VERDADERO.

Paso 3 - Devuelve Sí o nada

La Función IF luego devuelve "Sí" si la prueba lógica se evalúa como VERDADERA y nada si la prueba lógica devuelve FALSO.

SI (VERDADERO, "Sí", "")

devuelve "Sí" en la celda B3.

Volver arriba

Fórmula regular

La siguiente fórmula es bastante similar a la fórmula anterior, excepto que es una fórmula regular y tiene un adicional Función ÍNDICE.

=IF(OR(INDEX(COUNTIF(B3,"*"&$E$3:$E$7&"*"),)), "Yes", "")

Volver arriba

Descargar el archivo de Excel


SI-celda-contiene-texto-de-lista.xlsx

Volver arriba

Mostrar coincidencias si la celda contiene texto de la lista

Si la celda contiene valor de la lista

La imagen de arriba muestra una fórmula que verifica si una celda contiene un valor en la lista y luego devuelve ese valor. Si varios valores coinciden, se muestran todos los valores coincidentes en la lista.

Por ejemplo, la celda B3 contiene "ZDS, YNO, XBF" y el rango de celdas E3: E7 tiene dos valores que coinciden, "ZDS" y "XBF".

Fórmula en la celda C3:

=TEXTJOIN(", ", TRUE, IF(COUNTIF(B3, "*"&$E$3:$E$7&"*"), $E$3:$E$7, ""))

La función TEXTJOIN está disponible para suscriptores de Office 2019 y Office 365. Recibirá un error #NAME si su versión de Excel no tiene esta función. Los usuarios de Office 2019 pueden necesitar ingrese esta fórmula como una fórmula de matriz.

La siguiente fórmula funciona con la mayoría de las versiones de Excel.

Fórmula de matriz en la celda C3:

=INDEX($E$3:$E$7, MATCH(1, COUNTIF(B3, "*"&$E$3:$E$7&"*"), 0))

Sin embargo, solo devuelve el primer partido. Hay otra fórmula a continuación que devuelve todos los valores coincidentes, échale un vistazo.

Cómo ingresar una fórmula matricial

Volver arriba

Explicando la fórmula en la celda C3.

Paso 1: cuenta las celdas que contienen cadenas de texto

La Función COUNTIF le permite contar celdas en función de una condición, vamos a utilizar múltiples condiciones. Voy a usar asteriscos para hacer que la función COUNTIF verifique una coincidencia parcial.

El asterisco es uno de los dos caracteres comodín que puede usar, coincide con 0 (cero) a cualquier número de caracteres.

COUNTIF (B3, "*" y $ E $ 3: $ E $ 7 y "*")

se convierte en

COUNTIF ("ZDS, YNO, XBF", {"* MVN *"; "* QLL *"; "* BQX *"; "* ZDS *"; "* XBF *"})

y devuelve {1; 0; 0; 0; 1}.

Esta matriz contiene tantos valores como valores en la lista, la posición de cada valor en la matriz coincide con la posición del valor en la lista. Esto significa que podemos deducir de la matriz que el primer valor y el último valor se encuentra en la celda B3.

Paso 2 - Devuelve el valor real

La Función IF devuelve un valor si la prueba lógica es VERDADERA y otro valor si la prueba lógica es FALSA.

SI(prueba lógica, [value_if_true], [value_if_false])

Esto nos permite crear una matriz que contiene valores que existen en la celda B3.

IF (COUNTIF (B3, "*" & $ E $ 3: $ E $ 7 & "*"), $ E $ 3: $ E $ 7, "")

se convierte en

IF (COUNTIF ("ZDS, YNO, XBF", {"* MVN *"; "* QLL *"; "* BQX *"; "* ZDS *"; "* XBF *"}), {"* MVN * ";" * QLL * ";" * BQX * ";" * ZDS * ";" * XBF * "}," ")

y devuelve {""; ""; ""; "ZDS"; "XBF"}.

Paso 3 - Concatenar valores en la matriz

La TEXTJOIN La función le permite combinar cadenas de texto de múltiples rangos de celdas y también usar caracteres delimitadores si lo desea.

TEXTJOIN (delimitador, ignore_empty, text1, [text2], ...)

TEXTJOIN (",", TRUE, IF (COUNTIF (B3, "*" & $ E $ 3: $ E $ 7 & "*"), $ E $ 3: $ E $ 7, ""))

se convierte en

TEXTJOIN (",", TRUE, {""; ""; ""; "ZDS"; "XBF"})

y devuelve cadenas de texto ZDS, XBF.

Volver arriba

Versiones anteriores de Excel

Si la celda contiene el valor de la lista, muestre todos los valores coincidentes

La imagen de arriba muestra una fórmula que devuelve múltiples coincidencias si la celda contiene valores de una lista. Esta fórmula de matriz funciona con la mayoría de las versiones de Excel.

Fórmula de matriz en la celda C3:

=IFERROR(INDEX($G$3:$G$7, SMALL(IF(COUNTIF($B3, "*"&$G$3:$G$7&"*"), MATCH(ROW($G$3:$G$7), ROW($G$3:$G$7)), ""), COLUMNS($A$1:A1))), "")

Cómo ingresar una fórmula matricial

Copie la celda C3 y péguela en el rango de celdas C3: E15.

Volver arriba

Explicando la fórmula en la celda C3.

Paso 1 - Identificar valores coincidentes en la celda

La Función COUNTIF le permite contar celdas en función de una condición, vamos a utilizar un rango de celdas en su lugar. Esto devolverá una matriz de valores.

COUNTIF ($ B3, "*" y $ G $ 3: $ G $ 7 y "*")

se convierte en

COUNTIF ("ZDS, YNO, XBF", {"* MVN *"; "* QLL *"; "* BQX *"; "* ZDS *"; "* XBF *"})

y devuelve {0; 0; 0; 1; 1}.

Paso 2: calcule las posiciones relativas de los valores coincidentes

La Función IF devuelve un valor si la prueba lógica es VERDADERA y otro valor si la prueba lógica es FALSA.

SI(prueba lógica, [value_if_true], [value_if_false])

Esto nos permite crear una matriz que contiene valores que representan números de fila.

IF (COUNTIF ($ B3, "*" & $ G $ 3: $ G $ 7 & "*"), MATCH (ROW ($ G $ 3: $ G $ 7), ROW ($ G $ 3: $ G $ 7)), " ")

se convierte en

IF ({0; 0; 0; 2; 1}, MATCH (ROW ($ G $ 3: $ G $ 7), ROW ($ G $ 3: $ G $ 7)), "")

se convierte en

SI ({0; 0; 0; 2; 1}, {1; 2; 3; 4; 5}, "")

y devuelve {""; ""; ""; 4; 5}.

Paso 3: extrae el k-ésimo número más pequeño

Voy a usar el Pequeña función para poder extraer un valor en cada celda en el siguiente paso.

PEQUEÑA(matriz, k)

PEQUEÑO (IF (COUNTIF ($ B3, "*" & $ G $ 3: $ G $ 7 & "*"), MATCH (ROW ($ G $ 3: $ G $ 7), ROW ($ G $ 3: $ G $ 7)) , ""), COLUMNAS ($ A $ 1: A1)))

se convierte en

PEQUEÑO ({""; ""; ""; 4; 5}, COLUMNAS ($ A $ 1: A1)))

La función COLUMNAS calcula el número de columnas en un rango de celdas, sin embargo, la referencia de celda en nuestra fórmula crece cuando copia la celda y la pega en celdas adyacentes a la derecha.

PEQUEÑO ({0; 0; 0; 4; 5}, COLUMNAS ($ A $ 1: A1)))

se convierte en

PEQUEÑO ({""; ""; ""; 4; 5}, 1)

y devuelve 4.

Paso 4: valor de retorno basado en el número de fila

La Función ÍNDICE devuelve un valor de un rango de celdas o matriz, usted especifica qué valor en función de un número de fila y columna. Ambos [row_num] y [column_num] son opcionales

ÍNDICE(matriz, [row_num], [column_num])

ÍNDICE ($ G $ 3: $ G $ 7, PEQUEÑO (IF (COUNTIF ($ B3, "*" & $ G $ 3: $ G $ 7 & "*"), MATCH (ROW ($ G $ 3: $ G $ 7), ROW ($ G $ 3: $ G $ 7)), ""), COLUMNAS ($ A $ 1: A1)))

se convierte en

ÍNDICE ($ G $ 3: $ G $ 7, 4)

se convierte en

ÍNDICE ({"MVN"; "QLL"; "BQX"; "ZDS"; "XBF"}, 4)

y devuelve "ZDS" en la celda C3.

Paso 5 - Eliminar valores de error

La Función de IFERROR le permite detectar la mayoría de los errores en las fórmulas de Excel, excepto #¡DERRAMAR! errores. Tenga cuidado al usar la función IFERROR, ya que puede hacer que sea mucho más difícil detectar errores de fórmula.

IFERROR (valor, value_if_error)

Hay dos argumentos en la función IFERROR. El argumento del valor se devuelve si no está evaluando un error. los value_if_error se devuelve el argumento si el valor El argumento devuelve un error.

IFERROR (ÍNDICE ($ G $ 3: $ G $ 7, PEQUEÑO (IF (COUNTIF ($ B3, "*" & $ G $ 3: $ G $ 7 & "*"), MATCH (ROW ($ G $ 3: $ G $ 7) , FILA ($ G $ 3: $ G $ 7)), ""), COLUMNAS ($ A $ 1: A1))), "")

Volver arriba