Búsquedas bidireccionales con INDEX-MATCH-MATCH

Imagine una cuadrícula de ventas mensuales en la que las regiones aparecen a la izquierda y los meses en la parte superior. Quiere obtener el número correspondiente al punto en que se encuentran una región y un mes determinados.

Una búsqueda normal encuentra un valor recorriendo una sola dirección. Una búsqueda bidireccional busca en ambas direcciones a la vez: encuentra la fila y la columna correctas y después devuelve el valor situado en su intersección.

La herramienta clásica para esto es INDEX combinada con dos llamadas a MATCH, lo que suele escribirse como INDEX-MATCH-MATCH.

INDEX devuelve un valor de un rango según su posición. La forma completa es INDEX(array, row_num, column_num).

Proporciónele un bloque de celdas, un número de fila y un número de columna, y devolverá el valor situado en ese punto. Por ejemplo, en una cuadrícula que comienza en B2, solicitar la fila 3 y la columna 2 devuelve el valor situado 3 filas hacia abajo y 2 columnas hacia la derecha dentro de ese bloque.

La idea clave es que INDEX necesita posiciones, no etiquetas. Eso es exactamente lo que proporciona MATCH.

MATCH encuentra la posición de un valor dentro de una sola fila o columna. Su forma es MATCH(lookup_value, lookup_array, match_type).

Use 0 como tipo de coincidencia para una coincidencia exacta. El resultado es un número que indica dónde se encuentra el valor, contando desde 1.

Si "East" es el segundo elemento del rango A2:A5, MATCH devuelve 2. Ese 2 puede convertirse en el número de fila para INDEX.

Para realizar una búsqueda bidireccional, ejecute MATCH dos veces:

• Un MATCH encuentra en qué fila se encuentra la región.
• Otro MATCH encuentra en qué columna se encuentra el mes.

Después, proporcione ambos números a INDEX. El MATCH de la fila busca en un rango vertical de etiquetas; el MATCH de la columna busca en un rango horizontal de encabezados.

El resultado es la única celda en la que se cruzan esa fila y esa columna.

Imagine esta distribución. Las etiquetas de región se encuentran en A2:A5 (East, West, North, South). Los encabezados de mes están en B1:D1 (Jan, Feb, Mar). Las cifras de ventas se encuentran en B2:D5.

Dos celdas de entrada controlan la búsqueda: G1 contiene la región que desea y G2 contiene el mes que desea.

Nuestro objetivo es crear una sola fórmula que lea G1 y G2 y devuelva la cifra de ventas correspondiente de B2:D5.

Primero, localice la región. MATCH busca en la lista vertical de etiquetas A2:A5 el valor escrito en G1.

Si G1 contiene "North" y North es la tercera etiqueta, este MATCH devuelve 3.

Este número indica a INDEX qué fila del bloque de datos debe leer. Observe que buscamos en A2:A5, únicamente las etiquetas, no los datos; así, la posición 3 coincide con la tercera fila de datos.

A continuación, localice el mes. Este MATCH busca en la fila horizontal de encabezados B1:D1 el valor de G2.

Si G2 contiene "Feb" y Feb es el segundo encabezado, MATCH devuelve 2.

Ese número se convierte en la posición de columna para INDEX. Al igual que con las filas, buscamos únicamente en los encabezados B1:D1 para que la posición coincida con las columnas de datos de B2:D5.

Ahora incluya ambas llamadas a MATCH dentro de INDEX. El bloque de datos B2:D5 es la matriz; el MATCH de la fila proporciona el número de fila y el MATCH de la columna proporciona el número de columna.

Cuando G1 es "North" y G2 es "Feb", el MATCH de la fila devuelve 3 y el MATCH de la columna devuelve 2, por lo que INDEX devuelve el valor de la fila 3 y la columna 2 de B2:D5.

Esta única fórmula completa la búsqueda bidireccional.

Suponga que B2:D5 contiene lo siguiente: la fila de North es Jan 50, Feb 80 y Mar 65.

• MATCH("North", A2:A5, 0) devuelve 3.
• MATCH("Feb", B1:D1, 0) devuelve 2.
• INDEX(B2:D5, 3, 2) lee la fila 3 y la columna 2, y devuelve 80.

Cambie G1 a "East" o G2 a "Mar" y toda la fórmula se recalculará al instante. Ese es el poder de controlar INDEX mediante dos búsquedas MATCH.

VLOOKUP solo busca en la primera columna y devuelve un valor situado a un número fijo de columnas a la derecha. Para cambiar de mes, tendría que especificar de forma fija o calcular usted mismo el índice de columna.

INDEX-MATCH-MATCH permite elegir dinámicamente tanto la fila como la columna mediante una etiqueta. Puede reordenar columnas o insertar nuevos meses, y la fórmula seguirá funcionando porque busca el texto del encabezado, no un número fijo.

El error más común consiste en usar rangos que no coinciden. El rango de MATCH para la fila debe tener la misma altura que el bloque de datos de INDEX, y el rango de MATCH para la columna debe tener el mismo ancho.

A2:A5 tiene aquí 4 filas de alto y B2:D5 también tiene 4 filas, por lo que un resultado de MATCH igual a 3 significa realmente la tercera fila de datos. Si busca por accidente en A1:A5 (que incluye un encabezado), las posiciones se desplazan una fila y obtiene la celda equivocada.

Compruebe si ha entendido el patrón de búsqueda en dos direcciones.

Ha aprendido el patrón de búsqueda en dos direcciones:

• INDEX devuelve un valor situado en una posición de fila y columna dentro de un bloque.
• Un MATCH encuentra la fila buscando etiquetas verticales.
• Un segundo MATCH encuentra la columna buscando encabezados horizontales.

La fórmula combinada =INDEX(data, MATCH(row), MATCH(col)) lee dos entradas y devuelve el valor situado en su intersección. Mantenga los rangos de MATCH del mismo tamaño que el bloque de datos para evitar desajustes.