Recuperar el primer valor coincidente en la celda contra una lista
Suponga que tiene una lista de palabras clave, para recuperar la primera palabra clave que apareció en una celda específica, mientras que la celda contiene varios otros valores, tendrá que usar un ÍNDICE y PARTIDO fórmula con la ayuda de AGREGAR y BUSCAR funciones.
¿Cómo recuperar el primer valor coincidente en la celda contra una lista?
Para obtener el primera palabra clave coincidente en una celda con la lista de palabras clave como se muestra en la tabla anterior, significa que debemos realizar una coincidencia de contenido en lugar de una coincidencia exacta. Para hacerlo, puede utilizar la función BÚSQUEDA para introducir las posiciones de las palabras clave que aparecen en la celda en números numéricos en la función AGREGAR. Entonces AGGREGATE obtendrá el número más pequeño al establecer su function_num a 15 y el argumento ref2 a 1. Luego use COINCIDIR para ubicar el primer valor más pequeño y alimente el número de posición al ÍNDICE para recuperar el valor en esa posición.
Sintaxis genérica
=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))
√ Nota: Esta es una fórmula de matriz que requiere que ingrese con Ctrl + Shift + Participar.
- palabra clave_rng: El rango de celdas con palabras clave.
- buscar_celda: La celda a buscar si contiene las palabras clave.
Para recuperar el La primera palabra clave coincidente apareció en la celda B5 junto a la columna Palabras clave., copie o ingrese la fórmula a continuación en la celda C5, y presione Ctrl + Shift + Participar para obtener el resultado:
= ÍNDICE ($ E $ 5: $ E $ 7, COINCIDIR (AGREGAR (15,6, BÚSQUEDA ($ E $ 5: $ E $ 7,B5), 1), BÚSQUEDA ($ E $ 5: $ E $ 7,B5), 0))
√ Nota: Los signos de dólar ($) anteriores indican referencias absolutas, lo que significa que palabra clave_rng en la fórmula no cambiará cuando mueva o copie la fórmula a otras celdas. Sin embargo, no se agregan signos de dólar al buscar_celda ya que quieres que sea dinámico. Después de ingresar la fórmula, arrastre el controlador de relleno hacia abajo para aplicar la fórmula a las celdas siguientes.
Explicación de la fórmula
=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))
- BÚSQUEDA ($ E $ 5: $ E $ 7, B5): La función de BÚSQUEDA devuelve la posición de cada palabra clave del rango $ E $ 5: $ E $ 7 cuando se encuentra en números numéricos, y el #¡VALOR! error si no se encuentra. El resultado estaría en una matriz como esta: {15; 11; # ¡VALOR!}.
- AGREGADO (15,6,BÚSQUEDA ($ E $ 5: $ E $ 7, B5), 1) = AGREGADO (15,6,{15; 11; # ¡VALOR!}, 1): La función AGREGAR con un function_num de 15 y opción de 6 devolverá el valor más pequeño en la matriz de acuerdo con el ref2 argumento 1, mientras ignora los valores de error. Entonces, el fragmento volvería 11.
- PARTIDO(AGREGADO (15,6,BÚSQUEDA ($ E $ 5: $ E $ 7, B5), 1),BÚSQUEDA ($ E $ 5: $ E $ 7, B5), 0) = PARTIDO(11,{15; 11; # ¡VALOR!}, 0): La tipo_de_coincidencia 0 obliga a la función COINCIDIR a realizar una coincidencia exacta y devolver la posición de 11 en la matriz {15; 11; # ¡VALOR!}. Entonces, la función regresará 2.
- ÍNDICE ($ E $ 5: $ E $ 7,PARTIDO(AGREGADO (15,6,BÚSQUEDA ($ E $ 5: $ E $ 7, B5), 1),BÚSQUEDA ($ E $ 5: $ E $ 7, B5), 0)) = ÍNDICE ($ E $ 5: $ E $ 7,2): La función INDICE luego devuelve el 2nd valor en el rango $ E $ 5: $ E $ 7, cual es bbb.
Observación
- Si no hay palabras clave en una celda, un #NUM! error será devuelto
- La fórmula no distingue entre mayúsculas y minúsculas. Para realizar una coincidencia sensible a mayúsculas y minúsculas, simplemente puede reemplazar la función BÚSQUEDA con ENCONTRAR.
Funciones relacionadas
La función ÍNDICE de Excel devuelve el valor mostrado en función de una posición determinada de un rango o una matriz.
La función COINCIDIR de Excel busca un valor específico en un rango de celdas y devuelve la posición relativa del valor.
En Excel, la función BÚSQUEDA puede ayudarlo a encontrar la posición de un carácter específico o subcadena de la cadena de texto dada como se muestra en la siguiente captura de pantalla. En este tutorial, presentaré cómo usar esta función de BÚSQUEDA en Excel.
La función AGREGAR de Excel devuelve un agregado de cálculos como SUMA, CONTAR, PEQUEÑO, etc., con la opción de ignorar errores y filas ocultas.
Fórmulas relacionadas
Recuperar el primer valor de lista de una celda
Para recuperar la primera palabra clave que se enumera en un rango de una celda específica, mientras que la celda contiene uno de varios valores, tendrá que usar una fórmula de matriz bastante compleja con las funciones ÍNDICE, COINCIDIR, ESNÚMERO y BÚSQUEDA.
Coincidencia exacta con INDEX y MATCH
Si necesita averiguar la información que aparece en Excel sobre un producto, película o persona, etc., debe hacer un buen uso de la combinación de las funciones INDICE y COINCIDIR.
Verifica si una celda contiene un texto específico
Aquí, el tutorial proporciona algunas fórmulas para verificar si una celda contiene un texto específico y devolver VERDADERO y FALSO como se muestra a continuación, y explica los argumentos y cómo funcionan las fórmulas.
Compruebe si la celda contiene todas las cosas
Suponiendo que en Excel, hay una lista de valores en la columna E, desea verificar si las celdas en la columna B contienen todos los valores en la columna E y devolver VERDADERO o FALSO como se muestra a continuación. Aquí, en este tutorial, proporciona una fórmula para resolver esta tarea.
Compruebe si la celda contiene una de muchas cosas
Este tutorial proporciona una fórmula para verificar si una celda contiene uno de varios valores en Excel y explica los argumentos en la fórmula y cómo funciona la fórmula.
Las mejores herramientas de productividad de oficina
Kutools para Excel: te ayuda a destacar entre la multitud
Kutools para Excel cuenta con más de 300 funciones, Garantizar que lo que necesita está a sólo un clic de distancia...
Ficha Office: habilite la lectura y edición con pestañas en Microsoft Office (incluya Excel)
- ¡Un segundo para cambiar entre docenas de documentos abiertos!
- Reduzca cientos de clics del mouse para usted todos los días, diga adiós a la mano del mouse.
- Aumenta su productividad en un 50% al ver y editar varios documentos.
- Trae pestañas eficientes a Office (incluido Excel), al igual que Chrome, Edge y Firefox.
