Coincidencia aproximada bidireccional con múltiples criterios
En este tutorial, hablaremos sobre cómo buscar una coincidencia aproximada basada en varios criterios listados tanto en columnas como en filas en una hoja de cálculo de Excel, con la ayuda de las funciones INDICE, COINCIDIR, y SI.
¿Cómo realizar una coincidencia aproximada bidireccional con múltiples criterios en Excel?
Para encontrar la talla de ropa para una mujer que mide 165.5 cm de altura y pesa 55 kg según la tabla anterior, puedes agregar dos funciones COINCIDIR a la fórmula: una con una función SI obtiene el número de fila (sexo y altura), la otra obtiene el número de columna (peso). Luego, INDICE encontrará la talla correspondiente según las coordenadas. (Nota: El número de fila va antes del número de columna en una fórmula INDICE.)
Sintaxis genérica
=INDICE(rango_devuelto,COINCIDIR(valor_búsqueda1,SI(rango_búsqueda2=valor_búsqueda2,rango_búsqueda1),tipo_coincidencia),COINCIDIR(valor_búsqueda3,rango_búsqueda3,tipo_coincidencia))
√ Nota: Esta es una fórmula matricial que requiere que la ingreses con Ctrl + Shift + Enter.
- rango_devuelto: El rango donde deseas que la fórmula combinada devuelva el tamaño. Aquí se refiere al rango de tallas.
- valor_búsqueda: El valor que la fórmula utiliza para localizar la posición del tamaño correspondiente. Aquí se refiere a la información sobre el sexo, la altura y el peso dados.
- rango_búsqueda: El rango de celdas con los valores para comparar con el valor_búsqueda. Aquí se refiere a los rangos de sexo, altura y peso.
- tipo_coincidencia: 1 o -1.
1 u omitido (predeterminado), COINCIDIR encontrará el mayor valor que sea menor o igual al valor_búsqueda. Los valores en el rango_búsqueda deben estar colocados en orden ascendente.
-1, COINCIDIR encontrará el menor valor que sea mayor o igual al valor_búsqueda. Los valores en el rango_búsqueda deben estar colocados en orden descendente.
Para encontrar la talla de ropa para la mujer que mide 165.5 cm de altura y pesa 55 kg, copia o ingresa la siguiente fórmula en la celda M9, y presiona Ctrl + Shift + Enter para obtener el resultado:
=INDICE(D5:J16,COINCIDIR(M6,SI(B5:B16=M5,C5:C16),1),COINCIDIR(M7,D4:J4,1))
Explicación de la fórmula
=INDICE(D5:J16,COINCIDIR(M6,SI(B5:B16=M5,C5:C16),1),COINCIDIR(M7,D4:J4,1))
- SI(B5:B16=M5,C5:C16): La función SI verifica cada valor en B5:B16 si coinciden con el valor en la celda M5, Femenino. Si es así, la función SI mantendrá las alturas correspondientes listadas en C5:C16; si no, SI devolverá FALSOS. La fórmula devolverá 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_coincidencia 1 obliga a la función COINCIDIR a devolver la posición del mayor valor que sea menor o igual a 165.5 (el valor en la celda M6) en la matriz. Entonces, COINCIDIR devolverá 4, la posición del valor 165.
- COINCIDIR(M7,D4:J4,1): La función COINCIDIR devuelve 3, ya que el mayor valor que es menor o igual al valor en M7, 55, está en la 3ra posición del rango D4:J4.
- INDICE(D5:J16D5:J16,COINCIDIR(M6,SI(B5:B16=M5,C5:C16),1),COINCIDIR(M7,D4:J4,1)) = INDICE(D5:J16D5:J16,4,3): La función INDICE recupera el valor en la intersección de la cuarta fila y tercera columna en el rango de tallas D5:J16, que es el valor en la celda F8, M.
Funciones relacionadas
La función INDICE de Excel devuelve el valor mostrado basado en una posición dada 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.
La función SI es una de las funciones más simples y útiles en un libro de trabajo de Excel. Realiza una prueba lógica simple que depende del resultado de la comparación, y devuelve un valor si el resultado es VERDADERO, u otro valor si el resultado es FALSO.
Fórmulas relacionadas
Búsqueda bidireccional con INDICE y COINCIDIR
Para buscar algo tanto en filas como en columnas en Excel, o decimos para buscar un valor en la intersección de una fila y columna específicas, podemos usar la ayuda de las funciones INDICE y COINCIDIR.
Buscar coincidencia más cercana
Para buscar la coincidencia más cercana de un valor de búsqueda en un conjunto de datos numéricos en Excel, puedes usar las funciones INDICE, COINCIDIR, ABS y MIN juntas.
Buscar coincidencia más cercana con múltiples criterios
En algunos casos, es posible que necesites buscar la coincidencia más cercana o aproximada basada en más de un criterio. Con la combinación de las funciones INDICE, COINCIDIR y SI, puedes hacerlo rápidamente en Excel.
Búsqueda con múltiples criterios usando INDICE y COINCIDIR
Cuando se trata de una gran base de datos en una hoja de cálculo de Excel con varias columnas y encabezados de fila, siempre es complicado encontrar algo que cumpla con múltiples criterios. En este caso, puedes usar una fórmula matricial con las funciones INDICE y COINCIDIR.
Coincidencia aproximada con INDICE y COINCIDIR
Hay momentos en los que necesitamos encontrar coincidencias aproximadas en Excel para evaluar el desempeño de los empleados, calificar las puntuaciones de los estudiantes, calcular el costo de envío según el peso, etc. En este tutorial, hablaremos sobre cómo usar las funciones INDICE y COINCIDIR para recuperar los resultados que necesitamos.
Las Mejores Herramientas de Productividad para Office
Kutools para Excel - Te Ayuda a Sobresalir Entre la Multitud
Kutools para Excel Presume de Más de 300 Funciones, Asegurando Que Lo Que Necesitas Está a Solo Un Clic de Distancia...
Office Tab - Habilita Lectura y Edición con Pestañas en Microsoft Office (incluye Excel)
- ¡Un segundo para cambiar entre decenas de documentos abiertos!
- Reduce cientos de clics de ratón para ti todos los días, di adiós al síndrome del túnel carpiano.
- Aumenta tu productividad en un 50% al ver y editar múltiples documentos.
- Trae Pestañas Eficientes a Office (incluye Excel), Al Igual Que Chrome, Edge y Firefox.