Resumen
Para realizar una búsqueda de coincidencia aproximada bidireccional con varios criterios, puede utilizar una fórmula de matriz basada en INDICE y COINCIDIR, con la ayuda de la función SI para aplicar criterios. En el ejemplo que se muestra, la fórmula en K8 es:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
donde los datos (D6: H16), el diámetro (D5: H5), el material (B6: B16) y la dureza (C6: C16) son rangos nombrados que se utilizan solo por conveniencia.
Nota: esta es una fórmula de matriz y debe ingresarse con Control + Shift + Enter
Explicación
El objetivo es buscar una velocidad de avance basada en el material, la dureza y el diámetro de la broca. Los valores de la tasa de alimentación están en los datos del rango con nombre (D6: H16).
Esto se puede hacer con una fórmula de INDICE y COINCIDIR bidireccional. Una función COINCIDIR calcula el número de fila (material y dureza) y la otra función COINCIDIR encuentra el número de columna (diámetro). La función INDICE devuelve el resultado final.
En el ejemplo que se muestra, la fórmula en K8 es:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Se agregaron saltos de línea solo para facilitar la lectura).
El truco es que el material y la dureza deben manejarse juntos. Necesitamos restringir MATCH a los valores de dureza para un material dado (acero con bajo contenido de carbono en el ejemplo que se muestra).
Podemos hacer esto con la función SI. Esencialmente, usamos IF para "desechar" valores irrelevantes antes de buscar una coincidencia.
Detalles
La función INDICE recibe los datos del rango con nombre (D6: H16) como para la matriz. La primera función COINCIDIR calcula el número de fila:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Para ubicar la fila correcta, necesitamos hacer una coincidencia exacta en el material y una coincidencia aproximada en la dureza. Hacemos esto usando la función IF para filtrar primero la dureza irrelevante:
IF(material=K5,hardness) // filter
Probamos todos los valores del material (B6: B16) para ver si coinciden con el valor de K5 ("Acero con bajo contenido de carbono"). Si es así, se transfiere el valor de dureza. Si no, SI devuelve FALSO. El resultado es una matriz como esta:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Observe que los únicos valores supervivientes son los asociados con el acero con bajo contenido de carbono. Los otros valores ahora son FALSOS. Esta matriz se devuelve directamente a la función COINCIDIR como lookup_array.
El valor de búsqueda para la coincidencia proviene de K6, que contiene la dureza dada, 176. MATCH se configura para una coincidencia aproximada estableciendo match_type en 1. Con esta configuración, MATCH ignora los valores FALSE y devuelve la posición de una coincidencia exacta o el siguiente valor más pequeño .
Nota: los valores de dureza deben clasificarse en orden ascendente para cada material.
Con la dureza dada como 176, MATCH devuelve 6, entregado directamente a INDEX como el número de fila. Ahora podemos reescribir la fórmula original así:
=INDEX(data,6,MATCH(K7,diameter,1))
La segunda fórmula COINCIDIR encuentra el número de columna correcto realizando una coincidencia aproximada en el diámetro:
MATCH(K7,diameter,1) // get column num
Nota: los valores de diámetro D5: H5 deben clasificarse en orden ascendente.
El valor de búsqueda proviene de K7 (0,75) y lookup_array es el diámetro del rango con nombre (D5: H5).
Como antes, MATCH se establece para aproximarse a la coincidencia estableciendo match_type en 1.
Con el diámetro dado como 0,75, COINCIDIR devuelve 3, entregado directamente a la función INDICE como número de columna. La fórmula original ahora se resuelve en:
=INDEX(data,6,3) // returns 0.015
INDICE devuelve un resultado final de 0.015, el valor de F11.
