Tutorial de Excel: búsqueda bidireccional con INDEX y MATCH aproximados

Tabla de contenido

Atajos relacionados

En este video, veremos cómo construir una búsqueda bidireccional con INDEX y MATCH, usando una coincidencia aproximada.

Aquí tenemos una calculadora de costos simple, que busca el costo según el ancho y la altura de un material. La coincidencia debe ser aproximada. Por ejemplo, si el ancho es 250 y el alto es 325, el resultado correcto es $ 1,800.

Si el ancho es 450 y la altura permanece en 325, el resultado correcto es $ 3600.

Podemos construir una fórmula que haga esta búsqueda usando INDICE y COINCIDIR.

Primero, hagamos que INDEX funcione como una prueba de concepto, codificando parte de la fórmula. Esta es una excelente manera de asegurarse de tener la idea correcta antes de comenzar.

Entonces, con los datos en nuestra tabla como la matriz, y con un ancho de 450 y una altura de 325, INDEX necesitará un número de fila de 3 y un número de columna de 4, para recuperar el valor correcto en la tabla. . Esto funciona bien pero, por supuesto, no cambiará ya que los valores están codificados.

A continuación, configuremos las funciones COINCIDIR que necesitamos para calcular estos valores.

Para obtener el valor del ancho, que es el número de fila en INDICE, usaremos el valor de búsqueda de M7 y los valores de la columna B, como la matriz de búsqueda. Para el tipo de concordancia, queremos usar 1 para la concordancia aproximada porque los valores están ordenados en orden ascendente. El resultado es 4.

Para obtener la altura, que es la columna dentro de ÍNDICE, usaremos nuevamente COINCIDIR con el valor de M8, los valores de altura de la fila 6. Nuevamente, el tipo de coincidencia nuevamente se establece en 1 para una coincidencia aproximada. El resultado es 3.

Ahora, si cambio el ancho a 350 y el alto a 550, obtenemos un nuevo conjunto de resultados.

Estos valores son exactamente los que necesitamos para INDEX. Así que ahora simplemente copiaré y pegaré las funciones COINCIDIR en la fórmula INDICE original.

El ancho entra para el número de fila.

Y la altura entra para el número de columna.

Ahora tenemos una búsqueda dinámica que calcula correctamente el costo según el ancho y la altura, con una coincidencia aproximada.