Coincidencia aproximada bidireccional con múltiples criterios
En este tutorial explicaremos cómo buscar una coincidencia aproximada basada en varios criterios que aparecen tanto en columnas como en filas de una hoja de cálculo de Excel, con la ayuda de las funciones ÍNDICE, COINCIDIR y SI.
¿Cómo realizar una coincidencia aproximada bidireccional con varios criterios en Excel?
Para averiguar la talla de ropa de una mujer que mide 165,5 cm y pesa 55 kg según la tabla anterior, puede combinar dos funciones COINCIDIR en la fórmula: una, junto con la función SI, obtiene el número de fila (según sexo y altura), y la otra obtiene el número de columna (según el peso). A continuación, la función ÍNDICE localizará la talla correspondiente a partir de esas coordenadas. (Recuerde que, en una fórmula de ÍNDICE, el número de fila se indica antes que el número de columna.)
Sintaxis genérica
=INDEX()return_range,MATCH()lookup_value1,IF()lookup_array2=lookup_value2,lookup_array1),match_type),MATCH(lookup_value3,lookup_array3,match_type))
√ Nota: Esta es una fórmula matricial que requiere pulsar Ctrl+Mayús+Intro.
- rango_devolución: El rango del que desea que la fórmula de combinación devuelva el tamaño. En este caso, se refiere al rango de tallas.
- valor_buscado: El valor que la fórmula utiliza para localizar la posición del tamaño correspondiente. En este caso, se refiere a la información indicada sobre el sexo, la altura y el peso.
- matriz_buscada: El rango de celdas que contiene los valores con los que comparar el valor_buscado. En este caso, se refiere a los rangos de sexo, altura y peso.
- tipo_de_coincidencia: 1 o -1.
1 u omitido (valor predeterminado): COINCIDIR encuentra el valor más grande que sea menor o igual que el valor_buscado. Los valores en la matriz_buscada deben estar en orden ascendente.
-1: COINCIDIR encuentra el valor más pequeño que sea mayor o igual que el valor_buscado. Los valores en la matriz_buscada deben estar en orden descendente.
Para averiguar la talla de ropade la mujerque mide 165,5cm y pesa 55kg, copie o introduzca la siguiente fórmula en la celda M9 y pulse Ctrl+Mayús+Intropara obtener el resultado:
=ÍNDICE()D5:J16,COINCIDIR()M6,SI()B5:B16=M5,C5:C16),1),COINCIDIR(M7,D4:J4,1))
Explicación de la fórmula
=INDEX()D5:J16,MATCH(M6,)IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
- SI(B5:B16=M5,C5:C16): La función SI evalúa cada valor del rango B5:B16 para comprobar si coincide con el valor de la celda M5, es decir, Mujer. Cuando se cumple esta condición, la función conserva las alturas correspondientes del rango C5:C16; en caso contrario, devuelve FALSO. El resultado es una matriz como esta: {157;160;162,5;165;167,5;170;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO}.
- COINCIDIR(M6,)SI(B5:B16=M5,C5:C16),1)=COINCIDIR(M6,){157;160;162,5;165;167,5;170;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO},1):El tipo_de_coincidencia 1 obliga a la función COINCIDIR a devolver la posición del valor más grande que sea menor o igual que 165,5(el valor de la celda)M6) en la matriz. Por tanto, COINCIDIR devolverá 4, la posición del valor 165.
- COINCIDIR(M7,D4:J4,1):La función COINCIDIR devuelve 3, ya que el valor más grande menor o igual que el de M7 (55) se encuentra en la 3ª posición del rango D4:J4.
- ÍNDICE()D5:J16,COINCIDIR(M6,)SI(B5:B16=M5,C5:C16),1),COINCIDIR(M7,D4:J4,1)) = ÍNDICE(D5:J16:La función ÍNDICE recupera el valor situado en la intersección de la 4.ª fila y la 3.ª columna del rango de tallas D5:J16, que corresponde al valor de la celda F8: M.
Funciones relacionadas
La función ÍNDICE de Excel devuelve el Valor Mostrado en función de una posición dada dentro 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 de dicho valor.
La función SI es una de las más sencillas y útiles de Libro de Excel: realiza una prueba lógica simple y, según el resultado de la comparación, devuelve un valor si es VERDADERO o otro si es FALSO.
Fórmulas relacionadas
Búsqueda bidireccional con ÍNDICE y COINCIDIR
Para buscar algo simultáneamente en filas y columnas en Excel, es decir, para encontrar un valor en la intersección de una fila y una columna específicas, podemos utilizar las funciones ÍNDICE y COINCIDIR.
Buscar coincidencia más cercana
Para encontrar la coincidencia más cercana a un valor buscado dentro de un conjunto de datos numéricos en Excel, combine las funciones ÍNDICE, COINCIDIR, ABS y MIN.
Buscar el valor de coincidencia más cercana con varios criterios
En algunos casos, es posible que necesite buscar el valor de coincidencia más cercana o aproximada según varios criterios. Combinando las funciones ÍNDICE, COINCIDIR y SI, podrá hacerlo rápidamente en Excel.
Búsqueda con múltiples criterios mediante ÍNDICE y COINCIDIR
Cuando trabajas con una base de datos grande en una hoja de cálculo de Excel que incluye varias columnas y títulos de fila, suele resultar complicado localizar un elemento que cumpla varios criterios. En estos casos, puedes utilizar una fórmula matricial combinando las funciones ÍNDICE y COINCIDIR.
Coincidencia aproximada con ÍNDICE y COINCIDIR
A veces necesitamos encontrar coincidencias aproximadas en Excel para evaluar el rendimiento de los empleados, calificar las puntuaciones de los estudiantes, calcular gastos de envío según el peso, etc. En este tutorial, te mostraremos cómo usar las funciones ÍNDICE y COINCIDIR para obtener los resultados que buscas.
Las mejores herramientas de productividad para Office
Kutools para Excel - Le ayuda a destacar entre la multitud
Kutools para Excel Cuenta con más de 300 funciones,asegurando que lo que necesita esté siempre a un clic...
Office Tab - Habilita la lectura y edición con pestañas en Microsoft Office (incluido Excel)
- ¡Alterne entre decenas de documentos abiertos en un segundo!
- Reduzca cientos de clics del ratón cada día y despídase del síndrome del ratón.
- Aumente su productividad en un 50 % al ver y editar varios documentos simultáneamente.
- Lleva una navegación eficiente con pestañas a Office (incluido Excel), al estilo de Chrome, Edge y Firefox.