INDEX MATCH MATCH en Excel (Cómo realizar búsquedas en 2 dimensiones)

En esta publicación, quiero cubrir una de las funciones de búsqueda más poderosas disponibles en Excel, INDEX MATCH MATCH . En realidad, llamarla función es una terminología deficiente, ya que son tres funciones que se usan juntas dentro de una fórmula. Nos permite devolver un resultado. basado en una búsqueda de filas y columnas al mismo tiempo.

Si estás familiarizado con la función INDEX MATCH , este post no debería ser demasiado complicado para ti, ya que los principios son los mismos. Sin embargo, si no estás familiarizado con ella, no te preocupes, te lo explicaré todo. .

Tabla de contenido

• Cuándo utilizar PARTIDO ÍNDICE PARTIDO
• Aplicar la fórmula INDEX MATCH MATCH
• FÓSFORO
• ÍNDICE
• Anidando MATCH dentro de INDEX
• Errores comunes
• valor_buscado no encontrado en lookup_array
• Match_type incorrecto o faltante en la función MATCH
• MATCH lookup_array y la matriz INDEX no tienen el mismo tamaño
• Si aún no puede encontrar la causa del error
• PARTIDO ÍNDICE PARTIDO con Tablas
• ¿Por qué las tablas funcionan tan bien con INDEX MATCH MATCH?
• Usos avanzados de INDEX MATCH MATCH
• Devuelve un valor encima, debajo, a la izquierda o a la derecha del valor coincidente
• Devuelve la dirección de celda del valor coincidente
• Crear un rango dinámico
• Fórmula matricial para hacer coincidir múltiples criterios en filas y/o columnas
• INDEX MATCH MATCH con matrices dinámicas
• Doble BUSCAR XL como alternativa
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre del archivo: 0001 ÍNDICE PARTIDO PARTIDO.xlsx

Tener acceso

Cuándo utilizar PARTIDO ÍNDICE PARTIDO

Antes de profundizar en esta fórmula, veamos cuándo usarla.

La captura de pantalla anterior muestra el medallero de los Juegos Olímpicos de 2016. La lista en la Columna A muestra el nombre del país, con el recuento de medallas para cada país en las Columnas B a E.

Estos tipos de formatos de tabla son comunes para almacenar datos en una hoja de trabajo; una lista única de registros a la izquierda y una lista única de categorías en la parte superior.

¿Cómo podríamos usar una fórmula para buscar el número de medallas de bronce, plata, oro o el total de medallas recibidas por un solo país? Aquí es cuando recurriría a INDEX MATCH MATCH, ya que es, con diferencia, el método más simple y potente para realizar una búsqueda basada en filas y columnas.

Aplicar la fórmula INDEX MATCH MATCH

Para comprender cómo funciona este INDEX MATCH MATCH, consideraremos cada función individualmente y luego desarrollaremos la fórmula combinada.

FÓSFORO

La función COINCIDIR busca un elemento en una lista y luego devuelve la posición relativa del elemento dentro de esa lista. Usando nuestro ejemplo de los Juegos Olímpicos, si buscáramos Japón (JPN) en la lista de países en la columna A, devolvería 6, ya que es el sexto elemento de la lista.

La sintaxis de la función COINCIDIR es la siguiente:

=COINCIDIR(valor_buscado, matriz_buscada, [tipo_coincidencia])

• valor_buscado : el valor a coincidir. Puede ser un número, texto, valor lógico (es decir, verdadero/falso) o una referencia a una celda que contiene un número, texto o valor lógico.
• lookup_array : el rango de celdas en las que buscar el valor de búsqueda.
• [match_type] : un valor de 1, 0 o -1, que le indica a Excel cómo se debe realizar el cálculo de búsqueda. Los corchetes indican que este argumento es opcional. Sin embargo, si se omite un valor, Excel asumirá que match_type debe ser 1. Definitivamente, esta no es una suposición segura para Excel, por lo que siempre inserte un argumento, en lugar de dejar que se aplique el valor predeterminado .

El match_type es importante, ya que puede afectar el resultado del cálculo de formas inesperadas.

• 1 = la función devuelve el valor más grande que es menor o igual que el valor_buscado. Para usar esta opción, el conjunto_buscado debe estar en orden ascendente .
• -1 = la función devuelve el valor más pequeño que es mayor o igual al valor de búsqueda. Para usar esta opción, lookup_array debe estar en orden descendente .
• 0 = la función devuelve la primera coincidencia exacta encontrada. Para esta opción, lookup_array puede estar en cualquier orden.

Ejemplo usando la función COINCIDIR

La siguiente captura de pantalla muestra un ejemplo del uso de la función COINCIDIR para encontrar la posición de un valor de búsqueda.

La fórmula en la celda H5 es:

=COINCIDIR(H3,A2:A87,0)

• H3 = Japón (JPN) – el valor_buscado.
• A2:A87 = lista de países – lookup_array
• 0 = una coincidencia exacta – el tipo de coincidencia

El resultado en la celda H5 es 6. Japón (JPN) es el sexto país de la lista, por lo que la función COINCIDIR devuelve 6.

Nota importante: el resultado devuelto no es el número de fila, sino la enésima fila desde el inicio de lookup_array.

MATCH funciona con filas o columnas

La función COINCIDIR funciona igualmente bien con filas o columnas. Al usar esta misma función en todas las columnas, también podemos recuperar la posición de la palabra "Bronce".

La fórmula en la celda H9 es:

=COINCIDIR(H7,B1:E1,0)

• H7 = Bronce – el valor_buscado.
• B1:E1 = lista de medallas en las columnas: lookup_array
• 0 = una coincidencia exacta – el tipo de coincidencia

La cadena de texto 'Bronce' coincide con la tercera columna en el rango B1 a E1, por lo tanto, la función COINCIDIR devuelve 3 como resultado.

Resumen de la función PARTIDO

La siguiente imagen contiene un resumen de la función COINCIDIR.

Obtenga más información sobre la función MATCH en este artículo: Función MATCH (support.office.com)
Microsoft lanzó recientemente la función XMATCH, que puede tener en su versión de Excel. Obtenga más información sobre la función XMATCH aquí: Función XMATCH (support.office.com)

ÍNDICE

La función ÍNDICE devuelve la referencia a una celda basándose en una posición relativa determinada de fila o columna. Suena mucho más difícil de entender de lo que es. Por ejemplo, si ÍNDICE estuviera calculando la séptima celda dentro del rango A5:A15, el resultado sería celda A11. Tenga en cuenta que no sería A12, ya que el ÍNDICE comienza a contar desde 1. A5 es la primera celda del rango, lo que hace que A11 sea la séptima.

La función ÍNDICE tiene dos formas: (1) forma de matriz (2) forma de referencia. Podemos ver las dos formas cuando ingresamos la fórmula usando el cuadro de diálogo de la función de inserción.

Para esta publicación, nos centraremos completamente en la forma de matriz, que es, con diferencia, la más utilizada.

La sintaxis para la forma de matriz de la función ÍNDICE es la siguiente:

= ÍNDICE (matriz, núm_fila, [núm_columna])

• matriz : el rango de celdas desde las cuales encontrar la posición
• row_num : la enésima posición de la fila para ubicar en la matriz
• [column_num] – la enésima posición de la columna para ubicar en la matriz. Dentro de la función ÍNDICE, este es un argumento opcional pero es esencial para la combinación INDEX MATCH MATCH.

Ejemplo de uso de la función ÍNDICE

La siguiente captura de pantalla muestra un ejemplo del uso de la función ÍNDICE para encontrar el resultado según el país y el tipo de medalla.

La fórmula en la celda H11 es:

=ÍNDICE(B2:E87,H5,H9)

• B2:E87 = el rango de celdas para todo el medallero: la matriz.
• H5 = 6 – el resultado de la primera función COINCIDIR – el número_fila
• H9 = 3 – el resultado de la segunda función COINCIDIR – el column_num

La sexta fila y la tercera columna en el rango B2:E87 es la celda D7. Dado que D7 contiene el valor 21, el resultado de la función ÍNDICE es 21. La capacidad de la función ÍNDICE para devolver una referencia de celda es una característica importante que consideraremos más adelante en esta publicación.

Resumen de la función ÍNDICE

La siguiente imagen contiene un resumen de la función ÍNDICE.

Obtenga más información sobre la función ÍNDICE en este artículo: Función ÍNDICE (support.office.com)

Anidando MATCH dentro de INDEX

La buena noticia es que, si bien aquí funcionan tres funciones separadas, podemos colocar todas las funciones en una sola fórmula.

La fórmula en H13 es:

= ÍNDICE (B2: E87, COINCIDIR (H3, A2: A87,0), COINCIDIR (H7, B1: E1,0))

Para los argumentos núm_fila y núm_columna en las funciones ÍNDICE, insertamos las funciones COINCIDIR que creamos anteriormente.

Puede que haya parecido un poco aterrador, pero ahora que lo hemos desarrollado por etapas, espero que estés de acuerdo en que no es tan malo después de todo.

Errores comunes

Como hay tres funciones combinadas en una sola fórmula, solucionar errores puede ser un poco complicado, pero hay algunos problemas comunes que debes verificar primero.

valor_buscado no encontrado en lookup_array

El error más común con la función COINCIDIR es #N/A. Esto puede ocurrir donde:

• el tipo de coincidencia es 0 y el valor de búsqueda no se encuentra en el conjunto de búsqueda
• el primer valor en lookup_array es menor que lookup_value cuando se usa un match_type de -1
• el primer valor en lookup_array es mayor que lookup_value cuando se usa un match_type de 1
• el valor_buscado o matriz_buscada contiene valores formateados como texto, en lugar de números
• lookup_value o lookup_array contiene espacios iniciales o finales. Algunos caracteres no son visibles para el ojo humano (intente usar la función TRIM para eliminar los espacios).

Match_type incorrecto o faltante en la función MATCH

Usar el tipo de coincidencia incorrecto o excluir el tipo de coincidencia de la función COINCIDIR puede causar errores de cálculo. Estos son los peores tipos de errores, ya que puede parecer que la fórmula arroja el resultado correcto, pero no lo es. Verifique el resultado de su fórmula manualmente varias veces para asegurarse Asegúrese de que esté devolviendo el valor correcto.

El otro resultado probable de este problema es el error #N/A (ver arriba). Esto es mucho más útil porque sabemos que es un error.

MATCH lookup_array y la matriz INDEX no tienen el mismo tamaño

¡UN #REF! Puede ocurrir un error cuando se encuentra un MATCH, pero la matriz INDEX no es lo suficientemente grande como para incluir esa fila. Por ejemplo, si hay 10 filas en MATCH lookup_array, pero solo 5 filas en la matriz INDEX, un #REF! Se devolverá un error para cualquier PARTIDO en la posición 6 a 10.

Si aún no puede encontrar la causa del error

Si, después de probar estas opciones, aún no puede encontrar la causa del error, intente crear la fórmula a partir de las tres funciones individuales, como se muestra en los ejemplos anteriores.

PARTIDO ÍNDICE PARTIDO con Tablas

El mejor método para administrar los datos de la hoja de cálculo es en una tabla de Excel.

Las tablas introdujeron una nueva forma de hacer referencia a celdas y rangos. En lugar de utilizar la notación A1 estándar, utilizan referencias estructuradas, que se refieren a nombres de columnas, en lugar de celdas individuales. INDEX MATCH MATCH también se complace en trabajar con tablas.

La imagen de arriba proviene de la pestaña Ejemplo 2 del archivo de ejemplo. La fórmula en la celda H7 es: (para facilitar la lectura, se ha dividido en tres líneas a continuación).

=ÍNDICE(Datos Olímpicos[[Oro]:[Total]], PARTIDO(H3,Datos Olímpicos[País],0), PARTIDO(H5,Datos Olímpicos[[#Encabezados],[Oro]:[Total]],0))

• OlympicsData[[Gold]:[Total]] = las columnas de la tabla desde las cuales devolver el resultado: la matriz.
• H3 = Australia (AUS): el valor de búsqueda para buscar en las filas
• OlympicsData[Country] = lista de países – el lookup_array para las filas
• H5 = Plata: el valor de búsqueda para buscar en las columnas
• OlympicsData[[#Headers],[Gold]:[Total]] = los encabezados de las columnas desde Gold hasta Total. – el lookup_array para la columna

Las funciones COINCIDIR encontrarán el resultado en la décima fila y la segunda columna, lo que conduce a la celda C11. Dado que C11 contiene el valor 11, el resultado de INDEX MATCH MATCH es 11.

¿Por qué las tablas funcionan tan bien con INDEX MATCH MATCH?

Las referencias estructuradas son una de las mejores cosas de las tablas de Excel. Sabemos que el rango A2:A87 contiene celdas que enumeran los países, pero solo lo sabemos al observar los datos. Si solo miráramos la fórmula, sin prestar atención a los datos, no tendríamos idea de lo que se incluye en esas celdas.

Con referencias estructuradas (asumiendo que hemos usado nombres razonables para la tabla y las columnas), se hace referencia al rango usando el nombre de la tabla y la columna, como OlympicsData[País]. Sin siquiera mirar los datos, podemos tomar una buena idea. Adivine qué podría incluirse en ese rango. Compare la versión de tabla de la fórmula con la versión estándar de la fórmula. Estoy seguro de que estará de acuerdo en que la versión de tabla es mucho más fácil de entender.

Pero espere... hay otra característica fantástica de las tablas. Cualquier dato agregado al final de una tabla de Excel se incluye automáticamente dentro del rango. Pruébelo usted mismo, agregue algunos datos en la celda A88 de la hoja de cálculo del Ejemplo 2. La tabla se expandirse, al igual que la fórmula. Lo que significa que no es necesario cambiar la fórmula, aún funciona.

A continuación, intente ingresar datos en la celda A88 de la hoja de trabajo del Ejemplo 1, las fórmulas no se actualizan, por lo tanto, para incorporar esa fila adicional necesitaríamos cambiar las funciones ÍNDICE y COINCIDIR en consecuencia, lo cual es solo una pérdida de tiempo.

Bien vale la pena invertir tiempo en aprender a utilizar tablas en Excel.

Usos avanzados de INDEX MATCH MATCH

Muchos expertos en Excel recomendarán INDEX MATCH como mejor que BUSCARV . Del mismo modo, INDEX MATCH MATCH es mejor que VLOOKUP MATCH u otras combinaciones de funciones para búsquedas bidimensionales. Nos permite utilizar las siguientes técnicas avanzadas, creando una mayor flexibilidad en nuestros libros de Excel.

Devuelve un valor encima, debajo, a la izquierda o a la derecha del valor coincidente

INDEX MATCH MATCH puede encontrar el resultado arriba, abajo, izquierda o derecha del valor coincidente.

Utilice la pestaña Ejemplo 3 del libro de trabajo de ejemplo. Usaremos una fórmula para averiguar qué país está por encima de Japón (JPN) en el medallero y cuántas medallas de oro recibieron.

La fórmula en H7 resta 1 del resultado de la función COINCIDIR; luego, ÍNDICE encuentra el nombre del país.

=ÍNDICE(A2:A87,COINCIDIR(H3,A2:A87,0) - 1 )

Como puedes ver consultando la tabla, el país situado encima de Japón (JPN) es Alemania (GER), que es el resultado de la fórmula.

La fórmula en H9 se basa en esto para encontrar la cantidad de medallas de oro que recibió el país mencionado anteriormente.

= ÍNDICE (B2: E87, COINCIDIR (H3, A2: A87,0) - 1 , COINCIDIR (H5, B1: E1,0))

Usando el mismo método de restar 1 al resultado de la función PARTIDO, podemos calcular que el país por encima de Japón (JPN) recibió 17 medallas de oro.

Devuelve la dirección de celda del valor coincidente

La función ÍNDICE es mágica, ya que devuelve la dirección de la celda, en lugar de solo el valor de la celda.

Siguiendo con nuestro ejemplo anterior, ¿cómo sabemos de qué celda proviene el resultado? (es decir, ¿qué celda muestra las medallas de oro de Alemania?)

La fórmula en H11 usa la función CELDA para devolver la dirección:

=CELDA("dirección",ÍNDICE(B2:E87,COINCIDIR(H3,A2:A87,0) - 1,COINCIDIR(H5,B1:E1,0)))

Si intentamos incluir BUSCARV en la función CELDA, no funcionará. BUSCARV devuelve el valor de la celda, en lugar de la referencia de la celda. Esta es la magia de ÍNDICE

Crear un rango dinámico

Como hemos visto en el ejemplo anterior, la función ÍNDICE devuelve una referencia de celda; por lo tanto, podemos crear un rango dinámico.

Un rango en Excel se puede escribir como referencias de dos celdas cualesquiera separadas por dos puntos. Por ejemplo, B2: D4 es el rango de la celda B2 a la celda D4. Sin embargo, en lugar de utilizar la referencia de celda, podemos usar una función que devuelva una referencia de celda.

Mire la fórmula a continuación:

=SUMA(B2: ÍNDICE (B2:F8,3,3))

La función ÍNDICE se calcula en la celda D4. Esto equivale a:

=SUMA(B2 : D4)

Ahora que hemos establecido el concepto, pase a la pestaña Ejemplo 4.

Digamos que queremos saber cuántas medallas de oro y plata ganaron todos los países, desde China (CHN) hasta Italia (ITA).

La fórmula en la celda H7 es:

=SUM(ÍNDICE(B2:E87,COINCIDIR(H3,A2:A87,0),COINCIDIR(J3,B1:E1,0)) : ÍNDICE(B2:E87,COINCIDIR(H5,A2:A87,0),COINCIDIR (J5,B1:E1,0)))

Esta es una función SUMA estándar, donde el rango SUMA se basa en los resultados de las dos funciones ÍNDICE. ¿Notó el uso de dos puntos (:) entre las dos funciones ÍNDICE? Es esto lo que convierte dos cálculos en un rango.

Esa larga fórmula se reduce a lo siguiente:

=SUMA(B3:C10)

Cosas bastante sorprendentes, ¿verdad?

Fórmula matricial para hacer coincidir múltiples criterios en filas y/o columnas

En la pestaña Ejemplo 5, el medallero de los Juegos Olímpicos de 2012 ahora se agregó a los datos de 2016, con una columna de año agregada para diferenciar entre los dos. Nuestra lista de países ya no es única; cada nombre puede aparecer dos veces, una en 2012 y otra en 2016.

Al combinar el nombre del país con el año, aún podemos generar una referencia única nuevamente. Podríamos usar una columna auxiliar, pero en su lugar usaremos una fórmula matricial .

La fórmula en la celda I9 es:

={ÍNDICE(C2:F172,COINCIDIR( I3"-"I5 , A2:A172"-"B2:B172,0 ),COINCIDIR(I7,C1:F1,0))}

Consulte usted mismo la tabla: Rusia recibió 21 medallas de plata en 2012.

La línea mágica de la fórmula es la segunda línea. El valor_buscado en la primera función COINCIDIR se ha combinado con un guión intermedio como carácter espaciador. El conjunto_buscada también se ha unido con un guión como carácter espaciador. Este método convierte la fórmula en un tipo especial de fórmula conocida como fórmula matricial.

Las fórmulas de matriz no se pueden ingresar de la forma habitual (a menos que tenga una versión de Excel habilitada para matrices dinámicas, ver más abajo). Las llaves al principio ( { ) y al final ( } ) no son parte de la fórmula; no las ingrese en la barra de fórmulas . Las llaves se agregan automáticamente en Excel al presionar Ctrl + Shift + Enter .

También es posible utilizar varios criterios en los encabezados de las columnas. Esto significa que INDEX MATCH MATCH puede buscar un valor a partir de múltiples criterios en las filas y/o columnas.

INDEX MATCH MATCH con matrices dinámicas

Las matrices dinámicas son la nueva forma en que Excel devuelve resultados de fórmulas. Fueron anunciadas por Microsoft en septiembre de 2018 y se están implementando lentamente en diferentes versiones de Excel.

Si tiene una versión de Excel habilitada para matrices dinámicas, no es necesario presionar Ctrl + Shift + Enter para ingresar la fórmula INDEX MATCH MATCH en el ejemplo anterior. Excel entenderá la fórmula y calculará el resultado.

Sin embargo, si no tiene matrices dinámicas en su versión de Excel, mostrará el #¡VALOR! error.

Cualquiera que reciba su libro de Excel también necesitará la versión de matriz dinámica, así que tenga cuidado dónde las usa. Probablemente sea mejor seguir con Ctrl + Shift + Enter hasta que sepa que todos los que verán el archivo tienen una versión habilitada para matriz dinámica.

Doble BUSCAR XL como alternativa

Microsoft ha anunciado recientemente una nueva función llamada BUSCARX, que estará disponible en versiones más nuevas de Excel.BUSCARX tiene las ventajas de INDEX MATCH, pero con la simplicidad de BUSCARV.

Podemos anidar una BUSCAR XL dentro de otra BUSCAR XL para lograr el resultado equivalente de INDEX MATCH MATCH. Suena genial, ¿no? Sin embargo, al momento de escribir este artículo (noviembre de 2019), calculo que pasarán más de 3 años antes de que suficientes personas tengan una versión compatible para poder utilizarlo de forma fiable. Esté atento, ya que está por llegar

Obtenga más información sobre la función BUSCAR XL en este artículo: Función BUSCAR XL (support.office.com)

Conclusión

Si bien existen formas alternativas de realizar una búsqueda bidimensional, actualmente ninguna de ellas es tan poderosa como INDEX MATCH MATCH. Esta combinación de fórmulas es tan útil que debe estar dentro de su kit de herramientas de Excel.

Artículos Relacionados

• La verdadera razón por la que INDEX/MATCH es mejor que BUSCARV
• Cómo BUSCAR V a la izquierda
• Cómo cambiar imágenes basadas en valores de celda usando INDEX/MATCH

---