Combo de funciones INDICE & MATCH en Excel (10 ejemplos sencillos)

Excel tiene muchas funciones, alrededor de 450+ de ellas.

Y muchos de estos son simplemente increíbles. La cantidad de trabajo que puede hacer con algunas fórmulas todavía me sorprende (incluso después de haber usado Excel durante más de 10 años).

Y entre todas estas increíbles funciones, destaca el combo de funciones INDEX MATCH.

Soy un gran admirador del combo INDEX MATCH y lo he dejado bastante claro muchas veces.

Incluso escribí un artículo sobre Index Match Vs VLOOKUP que provocó un poco de debate (puede consultar la sección de comentarios para ver algunos fuegos artificiales).

Y hoy, estoy escribiendo este artículo centrado únicamente en Index Match para mostrarte algunos escenarios simples y avanzados en los que puedes usar esta poderosa combinación de fórmulas y hacer el trabajo.

Nota: Hay otras fórmulas de búsqueda en Excel, como BUSCARV y BUSCARH, y son excelentes. Muchas personas encuentran que BUSCARV es más fácil de usar (y eso también es cierto). Creo que INDEX MATCH es una mejor opción en muchos casos. Pero como la gente lo encuentra difícil, se usa menos. Entonces estoy tratando de simplificarlo usando este tutorial.

Ahora, antes de mostrarle cómo la combinación de INDEX MATCH está cambiando el mundo de los analistas y científicos de datos, permítanme primero presentarles las partes individuales: las funciones INDEX y MATCH.

Función INDICE: encuentra el valor basado en coordenadas

La forma más sencilla de comprender cómo funciona la función de índice es pensar en ella como un satélite GPS.

Tan pronto como le diga al satélite las coordenadas de latitud y longitud, sabrá exactamente dónde ir y encontrar esa ubicación.

Entonces, a pesar de tener una cantidad asombrosa de combinaciones de latitud, el satélite sabría exactamente dónde buscar.

Rápidamente hice una búsqueda de mi lugar de trabajo y esto es lo que obtuve.

De todos modos, basta de geografía.

Al igual que un satélite necesita coordenadas de latitud y longitud, la función INDICE en Excel necesitaría el número de fila y columna para saber a qué celda se refiere.

Y esa es la función ÍNDICE de Excel en pocas palabras.

Así que déjame definirlo en palabras sencillas para ti.

La función INDICE usará el número de fila y el número de columna para encontrar una celda en el rango dado y devolver el valor en ella.

ÍNDICE por sí solo es una función muy simple, sin utilidad. Después de todo, en la mayoría de los casos, no es probable que conozca los números de fila y columna.

Pero…

El hecho de que pueda usarlo con otras funciones (pista: COINCIDIR) que pueden encontrar el número de fila y el número de columna hace que INDEX sea una función de Excel extremadamente poderosa.

A continuación se muestra la sintaxis de la función INDICE:

= INDICE (matriz, núm_fila, [núm_columna]) = ÍNDICE (matriz, núm_fila, [núm_columna], [núm_área])

• matriz - a rango de celdas o una matriz constante.
• fila_num - el número de fila desde el que se obtendrá el valor.
• [col_num] - el número de columna desde el que se obtendrá el valor. Aunque este es un argumento opcional, pero si no se proporciona row_num, es necesario proporcionarlo.
• [area_num] - (Opcional) Si el argumento de la matriz está formado por varios rangos, este número se usaría para seleccionar la referencia de todos los rangos.

La función INDICE tiene 2 sintaxis (solo para su información).

El primero se utiliza en la mayoría de los casos. El segundo se usa solo en casos avanzados (como hacer una búsqueda de tres vías) que cubriremos en uno de los ejemplos más adelante en este tutorial.

Pero si es nuevo en esta función, recuerde la primera sintaxis.

A continuación se muestra un video que explica cómo usar la función INDICE

Función COINCIDIR: encuentra la posición basada en un valor de búsqueda

Volviendo a mi ejemplo anterior de longitud y latitud, COINCIDIR es la función que puede encontrar estas posiciones (en el mundo de las hojas de cálculo de Excel).

En lenguaje simple, la función COINCIDIR de Excel puede encontrar la posición de una celda en un rango.

¿Y sobre qué base encontraría la posición de una celda?

Basado en el valor de búsqueda.

Por ejemplo, si tiene una lista como la que se muestra a continuación y desea encontrar la posición del nombre "Marca" en ella, puede usar la función COINCIDIR.

La función devuelve 3, ya que esa es la posición de la celda con el nombre Mark en ella.

La función COINCIDIR comienza a buscar de arriba a abajo el valor de búsqueda (que es "Marca") en el rango especificado (que es A1: A9 en este ejemplo). Tan pronto como encuentra el nombre, devuelve la posición en ese rango específico.

A continuación se muestra la sintaxis de la función COINCIDIR en Excel.

= COINCIDIR (lookup_value, lookup_array, [match_type])

• valor de búsqueda - El valor para el que busca una coincidencia en lookup_array.
• lookup_array - El rango de celdas en el que está buscando el valor_buscado.
• [tipo de concordancia] - (Opcional) Esto especifica cómo Excel debe buscar un valor coincidente. Puede tomar tres valores -1, 0 o 1.

Comprender el argumento del tipo de concordancia en la función COINCIDIR

Hay una cosa adicional que necesita saber sobre la función COINCIDIR, y se trata de cómo pasa por los datos y encuentra la posición de la celda.

El tercer argumento de la función COINCIDIR puede ser 0, 1 o -1.

A continuación se muestra una explicación de cómo funcionan estos argumentos:

• 0: esto buscará una coincidencia exacta del valor. Si se encuentra una coincidencia exacta, la función COINCIDIR devolverá la posición de la celda. De lo contrario, devolverá un error.
• 1: encuentra el valor más grande que es menor o igual que el valor de búsqueda. Para que esto funcione, su rango de datos debe ordenarse en orden ascendente.
• -1: encuentra el valor más pequeño que es mayor o igual que el valor de búsqueda. Para que esto funcione, su rango de datos debe ordenarse en orden descendente.

A continuación se muestra un video que explica cómo usar la función COINCIDIR (junto con el argumento del tipo de coincidencia)

Para resumirlo y ponerlo en palabras simples:

• INDICE necesita la posición de la celda (número de fila y columna) y proporciona el valor de la celda.
• COINCIDIR encuentra la posición mediante un valor de búsqueda.

Combinémoslos para crear una potencia (ÍNDICE + COINCIDIR)

Ahora que tiene un conocimiento básico de cómo las funciones INDICE y COINCIDIR funcionan individualmente, combinemos estos dos y aprendamos sobre todas las cosas maravillosas que pueden hacer.

Para entender esto mejor, tengo algunos ejemplos que usan la combinación INDEX MATCH.

Comenzaré con un ejemplo simple y luego también le mostraré algunos casos de uso avanzados.

Haga clic aquí para descargar el archivo de ejemplo

Ejemplo 1: una búsqueda simple usando el combo INDEX MATCH

Hagamos una búsqueda simple con INDEX / MATCH.

A continuación se muestra una tabla donde tengo las calificaciones para diez estudiantes.

En esta tabla, quiero encontrar las marcas para Jim.

A continuación se muestra la fórmula que puede hacer esto fácilmente:

= ÍNDICE ($ A $ 2: $ B $ 11, COINCIDIR ("Jim", $ A $ 2: $ A $ 11,0), 2)

Ahora, si estás pensando que esto se puede hacer fácilmente usando una función BUSCARV, ¡tienes razón! Este no es el mejor uso de la genialidad de INDEX MATCH. A pesar de que soy fanático de INDEX MATCH, es un poco más difícil que VLOOKUP. Si lo único que desea hacer es obtener datos de una columna de la derecha, le recomiendo que utilice BUSCARV.

La razón por la que he mostrado este ejemplo, que también se puede hacer fácilmente con VLOOKUP, es para mostrarle cómo funciona INDEX MATCH en una configuración simple.

Ahora permítanme mostrarles un beneficio de INDEX MATCH.

Suponga que tiene los mismos datos, pero en lugar de tenerlos en columnas, los tiene en filas (como se muestra a continuación).

¿Sabes qué? Aún puedes usar el combo INDEX MATCH para obtener las calificaciones de Jim.

A continuación se muestra la fórmula que le dará el resultado:

= ÍNDICE ($ B $ 1: $ K $ 2,2, PARTIDA ("Jim", $ B $ 1: $ K $ 1,0))

Tenga en cuenta que debe cambiar el rango y cambiar las partes de fila / columna para que esta fórmula funcione también para datos horizontales.

Esto no se puede hacer con BUSCARV, pero aún puede hacerlo fácilmente con BUSCARH.

La combinación INDEX MATCH puede manejar fácilmente datos tanto horizontales como verticales.

Haga clic aquí para descargar el archivo de ejemplo

Ejemplo 2: búsqueda a la izquierda

Es más común de lo que cree.

Muchas veces, es posible que deba obtener los datos de una columna que está a la izquierda de la columna que tiene el valor de búsqueda.

Algo como se muestra a continuación:

Para conocer las ventas de Michael, deberá realizar una búsqueda a la izquierda.

Si estás pensando en BUSCARV, déjame detenerte allí mismo.

VLOOKUP no está hecho para buscar y obtener los valores de la izquierda.

¿Todavía puedes hacerlo usando BUSCARV?

¡Sí tu puedes!

Pero eso puede convertirse en una fórmula larga y fea.

Entonces, si desea hacer una búsqueda y obtener datos de las columnas de la izquierda, es mejor que use el combo INDEX MATCH.

A continuación se muestra la fórmula que obtendrá el número de ventas de Michael:

= ÍNDICE ($ A $ 2: $ C $ 11, COINCIDIR ("Michael", C2: C11,0), 2)

Otro punto aquí para INDEX MATCH. VLOOKUP puede recuperar los datos solo de las columnas que están a la derecha de la columna que tiene el valor de búsqueda.

Ejemplo 3: búsqueda bidireccional

Hasta ahora, hemos visto los ejemplos en los que queríamos obtener los datos de la columna adyacente a la columna que tiene el valor de búsqueda.

Pero en la vida real, los datos a menudo se distribuyen en varias columnas.

INDEX MATCH puede manejar fácilmente una búsqueda bidireccional.

A continuación se muestra un conjunto de datos de las calificaciones del estudiante en tres materias diferentes.

Si desea obtener rápidamente las calificaciones de un estudiante en las tres materias, puede hacerlo con INDEX MATCH.

La siguiente fórmula le dará las calificaciones de Jim para los tres temas (copie y pegue en una celda y arrastre para llenar otras celdas o copie y pegue en otras celdas).

= ÍNDICE ($ B $ 2: $ D $ 11, PARTIDA ($ F $ 3, $ A $ 2: $ A $ 11,0), PARTIDA (G $ 2, $ B $ 1: $ D $ 1,0))

Permítanme explicar rápidamente también esta fórmula.

La fórmula de ÍNDICE usa B2: D11 como rango.

El primer PARTIDO usa el nombre (Jim en la celda F3) y busca su posición en la columna de nombres (A2: A11). Este se convierte en el número de fila desde el que se deben obtener los datos.

La segunda fórmula COINCIDIR usa el nombre del sujeto (en la celda G2) para obtener la posición de ese nombre de sujeto específico en B1: D1. Por ejemplo, Matemáticas es 1, Física es 2 y Química es 3.

Dado que estas posiciones de MATCH se introducen en la función INDICE, devuelve la puntuación según el nombre del estudiante y el nombre de la materia.

Esta fórmula es dinámica, lo que significa que si cambia el nombre del estudiante o los nombres de las materias, aún funcionará y obtendrá los datos correctos.

Una gran cosa sobre el uso de INDICE / MATCH es que incluso si intercambia los nombres de los sujetos, seguirá dando el resultado correcto.

Ejemplo 4: valor de búsqueda de varias columnas / criterios

Suponga que tiene un conjunto de datos como se muestra a continuación y desea obtener las marcas para "Mark Long".

Dado que los datos están en dos columnas, no puedo buscar a Mark y obtener los datos.

Si lo hago de esa manera, obtendré los datos de las marcas para Mark Frost y no para Mark Long (porque la función COINCIDIR me dará el resultado de la MARCA que cumple).

Una forma de hacerlo es crear una columna auxiliar y combinar los nombres. Una vez que tenga la columna de ayuda, puede usar BUSCARV y obtener los datos de las marcas.

Si está interesado en aprender a hacer esto, lea este tutorial sobre el uso de BUSCARV con varios criterios.

Pero con el combo INDEX / MATCH, no necesita una columna de ayuda. Puede crear una fórmula que maneje varios criterios en la propia fórmula.

La siguiente fórmula dará el resultado.

= ÍNDICE ($ C $ 2: $ C $ 11, COINCIDIR ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))

Permítanme explicarles rápidamente qué hace esta fórmula.

La parte COINCIDIR de la fórmula combina el valor de búsqueda (Mark y Long) así como la matriz de búsqueda completa. Cuando se usa $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 como matriz de búsqueda, en realidad verifica el valor de búsqueda con la cadena combinada de nombre y apellido (separados por el símbolo de barra vertical).

Esto asegura que obtenga el resultado correcto sin utilizar columnas auxiliares.

También puede hacer este tipo de búsqueda (donde hay múltiples columnas / criterios) con BUSCARV, pero necesita usar una columna auxiliar. El combo INDEX MATCH hace que sea un poco fácil hacer esto sin columnas auxiliares.

Ejemplo 5: obtener valores de toda la fila / columna

En los ejemplos anteriores, hemos utilizado la función INDICE para obtener el valor de una celda específica. Proporciona el número de fila y columna, y devuelve el valor en esa celda específica.

Pero puedes hacer más.

También puede utilizar la función INDICE para obtener los valores de una fila o columna completa.

¡Y en qué puede ser útil esto, preguntas!

Suponga que desea conocer la puntuación total de Jim en las tres materias.

Puede usar la función INDICE para obtener primero todas las calificaciones de Jim y luego usar la función SUMA para obtener un total.

Veamos cómo hacer esto.

A continuación tengo los puntajes de todos los estudiantes en tres materias.

La siguiente fórmula me dará la puntuación total de Jim en las tres materias.

= SUMA (ÍNDICE ($ B $ 2: $ D $ 11, PARTIDA ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))

Déjame explicarte cómo funciona esta fórmula.

El truco aquí es usar 0 como número de columna.

Cuando usa 0 como número de columna en la función INDICE, devolverá todos los valores de fila. De manera similar, si usa 0 como número de fila, devolverá todos los valores de la columna.

Entonces, la parte siguiente de la fórmula devuelve una matriz de valores: {97, 70, 73}

ÍNDICE ($ B $ 2: $ D $ 11, PARTIDA ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)

Si solo ingresa esta fórmula anterior en una celda en Excel y presiona enter, verá un #VALOR! error. Esto se debe a que no devuelve un valor único, sino una matriz de valores.

Pero no se preocupe, la matriz de valores sigue ahí. Puede verificar esto seleccionando la fórmula y presionando la tecla F9. Le mostrará el resultado de la fórmula, que en este caso es una matriz de tres valores: {97, 70, 73}

Ahora, si envuelve esta fórmula de ÍNDICE en la función SUMA, le dará la suma de todas las calificaciones obtenidas por Jim.

También puede usar el mismo para obtener las calificaciones más altas, más bajas y promedio de Jim.

Al igual que hemos hecho esto para un estudiante, también puede hacerlo para una asignatura. Por ejemplo, si desea obtener el puntaje promedio en un tema, puede mantener el número de fila como 0 en la fórmula INDICE y le dará todos los valores de columna de ese tema.

Haga clic aquí para descargar el archivo de ejemplo

Ejemplo 6: Encuentre la calificación del estudiante (técnica de coincidencia aproximada)

Hasta ahora, hemos utilizado la fórmula COINCIDIR para obtener la coincidencia exacta del valor de búsqueda.

Pero también puedes usarlo para hacer una coincidencia aproximada.

Ahora, ¿qué diablos es la Coincidencia Aproximada?

Dejame explicar.

Cuando busca cosas como nombres o identificadores, busca una coincidencia exacta. Pero a veces, necesita conocer el rango en el que se encuentran sus valores de búsqueda. Este suele ser el caso de los números.

Por ejemplo, como profesor de clase, es posible que desee saber cuál es la calificación de cada alumno en una materia y la calificación se decide en función de la calificación.

A continuación se muestra un ejemplo, donde quiero la calificación para todos los estudiantes y la calificación se decide según la tabla de la derecha.

Entonces, si un estudiante obtiene menos de 33, la calificación es F y si obtiene menos de 50 pero más de 33, es E, y así sucesivamente.

A continuación se muestra la fórmula que hará esto.

= ÍNDICE ($ F $ 3: $ F $ 8, PARTIDA (B2, $ E $ 3: $ E $ 8,1), 1)

Déjame explicarte cómo funciona esta fórmula.

En la función COINCIDIR, hemos utilizado 1 como argumento [tipo_coincidencia]. Este argumento devolverá el valor más grande que sea menor o igual al valor de búsqueda.

Esto significa que la fórmula COINCIDIR pasa por el rango de marcas, y tan pronto como encuentre un rango de marcas que sea igual o menor que el valor de las marcas de búsqueda, se detendrá allí y devolverá su posición.

Entonces, si el valor de la marca de búsqueda es 20, la función COINCIDIR devolvería 1 y si es 85, devolvería 5.

Y la función INDICE usa este valor de posición para obtener la calificación.

IMPORTANTE: Para que esto funcione, sus datos deben ordenarse en orden ascendente. Si no es así, puede obtener resultados incorrectos.

Tenga en cuenta que lo anterior también se puede hacer usando la siguiente fórmula VLOOKUP:

= BUSCARV (B2, $ E $ 3: $ F $ 8,2, VERDADERO)

Pero la función COINCIDIR puede ir un paso más allá cuando se trata de una coincidencia aproximada.

También puede tener datos descendentes y puede usar el combo INDEX MATCH para encontrar el resultado. Por ejemplo, si cambio el orden de la tabla de calificaciones (como se muestra a continuación), aún puedo encontrar las calificaciones de los estudiantes.

Para hacer esto, todo lo que tengo que hacer es cambiar el argumento [match_type] a -1.

A continuación se muestra la fórmula que he utilizado:

= ÍNDICE ($ F $ 3: $ F $ 8, COINCIDIR (B2, $ E $ 3: $ E $ 8, -1), 1)

VLOOKUP también puede hacer una coincidencia aproximada, pero solo cuando los datos se ordenan en orden ascendente (pero no funciona si los datos se ordenan en orden descendente).

Ejemplo 7: Búsquedas sensibles a mayúsculas y minúsculas

Hasta ahora, todas las búsquedas que hemos realizado no distinguen entre mayúsculas y minúsculas.

Esto significa que si el valor de búsqueda era Jim, JIM o jim, no importaba. Obtendrá el mismo resultado.

Pero, ¿qué sucede si desea que la búsqueda distinga entre mayúsculas y minúsculas?

Este suele ser el caso cuando tiene grandes conjuntos de datos y la posibilidad de repetición o nombres / identificadores distintos (siendo la única diferencia el caso)

Por ejemplo, suponga que tengo el siguiente conjunto de datos de estudiantes donde hay dos estudiantes con el nombre Jim (la única diferencia es que uno se ingresa como Jim y el otro como Jim).

Tenga en cuenta que hay dos estudiantes con el mismo nombre: Jim (celda A2 y A5).

Dado que una búsqueda normal no funcionaría, debe realizar una búsqueda que distinga entre mayúsculas y minúsculas.

A continuación se muestra la fórmula que le dará el resultado correcto. Dado que esta es una fórmula de matriz, debe usar Control + Shift + Enter.

= ÍNDICE ($ B $ 2: $ B $ 11, COINCIDIR (VERDADERO, EXACTO (D3, A2: A11), 0), 1)

Déjame explicarte cómo funciona esta fórmula.

La función EXACTA busca una coincidencia exacta del valor de búsqueda (que es "jim" en este caso). Pasa por todos los nombres y devuelve FALSE si no es una coincidencia y VERDADERO si es una coincidencia.

Entonces, la salida de la función EXACTA en este ejemplo es: {FALSO; FALSO; FALSO; VERDADERO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO}

Tenga en cuenta que solo hay un VERDADERO, que es cuando la función EXACTA encontró una coincidencia perfecta.

La función COINCIDIR luego encuentra la posición de VERDADERO en la matriz devuelta por la función EXACTA, que es 4 en este ejemplo.

Una vez que tenemos la posición, la función INDICE la usa para encontrar las marcas.

Ejemplo 8: Encuentre la coincidencia más cercana

Avancemos un poco ahora.

Suponga que tiene un conjunto de datos donde desea encontrar a la persona que tiene la experiencia laboral más cercana a la experiencia requerida (mencionada en la celda D2).

Si bien las fórmulas de búsqueda no están diseñadas para hacer esto, puede combinarlas con otras funciones (como MIN y ABS) para hacerlo.

A continuación se muestra la fórmula que encontrará a la persona con la experiencia más cercana a la requerida y devolverá el nombre de la persona. Tenga en cuenta que la experiencia debe ser la más cercana (que puede ser menor o mayor).

= ÍNDICE ($ A $ 2: $ A $ 15, COINCIDIR (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))

Dado que esta es una fórmula de matriz, debe usar Control + Shift + Enter.

El truco de esta fórmula es cambiar el valor de búsqueda y la matriz de búsqueda para encontrar la diferencia de experiencia mínima en los valores requeridos y reales.

Antes de explicar la fórmula, comprendamos cómo lo haría manualmente.

Pasará por cada celda de la columna B y encontrará la diferencia en la experiencia entre lo que se requiere y la que tiene una persona. Una vez que tenga todas las diferencias, encontrará la mínima y buscará el nombre de esa persona.

Esto es exactamente lo que estamos haciendo con esta fórmula.

Dejame explicar.

El valor de búsqueda en la fórmula COINCIDIR es MIN (ABS (D2-B2: B15)).

Esta parte le da la diferencia mínima entre la experiencia dada (que es de 2.5 años) y todas las demás experiencias. En este ejemplo, devuelve 0.3

Tenga en cuenta que he usado ABS para asegurarme de que estoy buscando el más cercano (que puede ser más o menos que la experiencia dada).

Ahora, este valor mínimo se convierte en nuestro valor de búsqueda.

La matriz de búsqueda en la función COINCIDIR es ABS (D2- $ B $ 2: $ B $ 15).

Esto nos da una matriz de números de los que se ha restado 2.5 (la experiencia requerida).

Entonces ahora tenemos un valor de búsqueda (0.3) y una matriz de búsqueda ({6.8; 0.8; 19.5; 21.8; 14.5; 11.2; 0.3; 9.2; 2; 9.8; 14.8; 0.4; 23.8; 2.9})

La función COINCIDIR encuentra la posición de 0.3 en esta matriz, que también es la posición del nombre de la persona que tiene la experiencia más cercana.

Este número de posición es luego usado por la función INDICE para devolver el nombre de la persona.

Lectura relacionada: Encuentre la coincidencia más cercana en Excel (ejemplos que usan fórmulas de búsqueda)

Haga clic aquí para descargar el archivo de ejemplo

Ejemplo 9: Use INDEX MATCH con caracteres comodín

Si desea buscar un valor cuando hay una coincidencia parcial, debe utilizar caracteres comodín.

Por ejemplo, a continuación se muestra un conjunto de datos del nombre de la empresa y sus capitalizaciones de mercado y desea obtener la capitalización de mercado. datos de las tres empresas de la derecha.

Dado que no son coincidencias exactas, no puede realizar una búsqueda regular en este caso.

Pero aún puede obtener los datos correctos usando un asterisco (*), que es un carácter comodín.

A continuación se muestra la fórmula que le proporcionará los datos al hacer coincidir los nombres de las empresas de la columna principal y obtener la cifra de capitalización de mercado correspondiente.

= ÍNDICE ($ B $ 2: $ B $ 10, COINCIDIR (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)

Déjame explicarte cómo funciona esta fórmula.

Dado que no hay una coincidencia exacta de los valores de búsqueda, he usado D2 & ”*” como el valor de búsqueda en la función COINCIDIR.

Un asterisco es un carácter comodín que representa cualquier número de caracteres. Esto significa que Apple * en la fórmula significaría cualquier cadena de texto que comience con la palabra Apple y pueda tener cualquier número de caracteres después.

Así que cuando Manzana* se utiliza como valor de búsqueda y la fórmula COINCIDIR lo busca en la columna A, devuelve la posición de "Apple Inc.", ya que comienza con la palabra Apple.

También puede utilizar caracteres comodín para buscar cadenas de texto en las que el valor de búsqueda se encuentra en el medio. Por ejemplo, si usa * Apple * como valor de búsqueda, encontrará cualquier cadena que tenga la palabra manzana en cualquier lugar.

Nota: esta técnica funciona bien cuando solo tiene una instancia de coincidencia. Pero si tiene varias instancias de coincidencia (por ejemplo, Apple Inc y Apple Corporation, entonces la función COINCIDIR devolvería la posición de la primera instancia coincidente únicamente.

Ejemplo 10: búsqueda de tres vías

Este es un uso avanzado de INDEX MATCH, pero lo cubriré para mostrarte el poder de esta combinación.

Recuerde que dije que la función INDICE tiene dos sintaxis:

= INDICE (matriz, núm_fila, [núm_columna]) = ÍNDICE (matriz, núm_fila, [núm_columna], [núm_área])

Hasta ahora, en todos nuestros ejemplos, solo hemos utilizado el primero.

Pero para una búsqueda de tres vías, necesita usar la segunda sintaxis.

Permítanme explicar primero qué significa una mirada de tres vías.

En una búsqueda bidireccional, usamos la fórmula COINCIDIR ÍNDICE para obtener las calificaciones cuando tenemos el nombre del estudiante y el nombre de la materia. Por ejemplo, buscar las marcas de Jim en matemáticas es una búsqueda bidireccional.

Una mirada de tres vías le agregaría otra dimensión. Por ejemplo, suponga que tiene un conjunto de datos como se muestra a continuación y desea saber la puntuación de Jim en Matemáticas en el examen de mitad de período, entonces esta sería una búsqueda de tres vías.

A continuación se muestra la fórmula que dará el resultado.

= ÍNDICE (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), PARTIDA ($ F $ 5, $ A $ 3: $ A $ 7,0), PARTIDA (G $ 4 , $ B $ 2: $ D $ 2,0), (IF (G $ 3 = "Prueba unitaria", 1, IF (G $ 3 = "Mid Term", 2,3))))

La fórmula anterior verificaba tres cosas: el nombre del estudiante, la materia y el examen. Una vez que encuentra el valor correcto, lo devuelve en la celda.

Permítanme explicar cómo funciona esta fórmula dividiendo la fórmula en partes.

• matriz - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): En lugar de usar una sola matriz, en este caso, he usado tres matrices entre paréntesis.
• número_fila - COINCIDIR ($ F $ 5, $ A $ 3: $ A $ 7,0): La función COINCIDIR se usa para encontrar la posición del nombre del estudiante en la celda $ F $ 5 en la lista del nombre del estudiante.
• col_num - COINCIDIR (G $ 4, $ B $ 2: $ D $ 2,0): La función COINCIDIR se usa para encontrar la posición del nombre del sujeto en la celda $ B $ 2 en la lista de nombres del sujeto.
• [area_num] - IF (G $ 3 = "Prueba unitaria", 1, IF (G $ 3 = "Mid Term", 2,3)): El valor del número de área le dice a la función INDICE cuál de las tres matrices usar para obtener el valor. Si el examen es Término unitario, la función SI devolverá 1 y la función INDICE usará la primera matriz para obtener el valor. Si el examen es de mitad de período, la fórmula IF devolverá 2, de lo contrario, devolverá 3.

Este es un ejemplo avanzado del uso de INDEX MATCH, y es poco probable que encuentre una situación en la que tenga que usar esto. Pero es bueno saber qué pueden hacer las fórmulas de Excel.

Haga clic aquí para descargar el archivo de ejemplo

¿Por qué INDEX / MATCH es mejor que VLOOKUP?

¿O es eso?

Sí, lo es, en la mayoría de los casos.

Presentaré mi caso en un momento.

Pero antes de hacer eso, déjame decirte esto: BUSCARV es una función extremadamente útil y me encanta. Puede hacer muchas cosas en Excel y yo mismo lo uso de vez en cuando. Dicho esto, no significa que no pueda haber nada mejor, y INDEX / MATCH (con más flexibilidad y funcionalidades) es mejor.

Entonces, si desea hacer una búsqueda básica, es mejor que use BUSCARV.

INDEX / MATCH es BUSCARV con esteroides. Y una vez que aprenda INDICE / MATCH, es posible que siempre prefiera usarlo (especialmente debido a la flexibilidad que tiene).

Sin estirarlo demasiado, permítame darle rápidamente las razones por las que INDICE / MATCH es mejor que BUSCARV.

INDEX / MATCH puede mirar a la izquierda (así como a la derecha) del valor de búsqueda

Lo cubrí en uno de los ejemplos anteriores.

Si tiene un valor que está a la izquierda del valor de búsqueda, no puede hacerlo con BUSCARV

Al menos no solo con BUSCARV.

Sí, puede combinar BUSCARV con otras fórmulas y hacerlo, pero se vuelve complicado y desordenado.

INDEX / MATCH, por otro lado, está hecho para buscar en todas partes (ya sea a la izquierda, a la derecha, arriba o abajo)

INDEX / MATCH puede funcionar con rangos verticales y horizontales

Nuevamente, con pleno respeto a BUSCARV, no está hecho para hacer esto.

Después de todo, la V en VLOOKUP significa vertical.

VLOOKUP solo puede atravesar datos que son verticales, mientras que INDEX / MATCH puede atravesar datos tanto vertical como horizontalmente.

Por supuesto, existe la función HLOOKUP para encargarse de la búsqueda horizontal, pero no es VLOOKUP entonces … ¿verdad?

Me gusta el hecho de que el combo INDEX MATCH es lo suficientemente flexible como para trabajar con datos verticales y horizontales.

VLOOKUP no puede funcionar con datos descendentes

Cuando se trata de la coincidencia aproximada, BUSCARV e INDICE / COINCIDIR están al mismo nivel.

Pero INDEX MATCH toma el punto, ya que también puede manejar datos que están en orden descendente.

Muestro esto en uno de los ejemplos de este tutorial donde tenemos que encontrar la calificación de los estudiantes en función de la tabla de calificaciones. Si la tabla está ordenada en orden descendente, VLOOKUP no funcionaría (pero INDEX MATCH sí lo haría).

INDEX / MATCH puede ser un poco más rápido

Seré sincero. Yo no realicé esta prueba.

Confío en la sabiduría de un maestro de Excel: Charley Kyd.

La diferencia de velocidad en VLOOKUP e INDEX / MATCH apenas se nota cuando tiene pequeños conjuntos de datos. Pero si tiene miles de filas y muchas columnas, esto puede ser un factor decisivo.

En su artículo, Charley Kyd afirma:

“En el peor de los casos, el método INDEX-MATCH es tan rápido como BUSCARV; en el mejor de los casos, es mucho más rápido ".

INDEX / MATCH es independiente de la posición real de la columna

Si tiene un conjunto de datos como se muestra a continuación mientras está obteniendo la puntuación de Jim en Física, puede hacerlo usando BUSCARV.

Y para hacer eso, puede especificar el número de columna como 3 en BUSCARV.

Todo está bien.

Pero, ¿qué pasa si elimino la columna Math?

En ese caso, la fórmula BUSCARV se romperá.

¿Por qué? - Porque estaba codificado para usar la tercera columna, y cuando elimino una columna intermedia, la tercera columna se convierte en la segunda columna.

Usar INDEX / MATCH, en este caso, es mejor ya que puede hacer que el número de columna sea dinámico usando MATCH. Entonces, en lugar de un número de columna, verifica el nombre del sujeto y lo usa para devolver el número de columna.

Seguramente puede hacerlo combinando VLOOKUP con MATCH, pero si combina de todos modos, ¿por qué no hacerlo con INDEX, que es mucho más flexible?

Cuando usa INDEX / MATCH, puede insertar / eliminar columnas de forma segura en su conjunto de datos.

A pesar de todos estos factores, hay una razón por la que BUSCARV es tan popular.

Y es una gran razón.

VLOOKUP es más fácil de usar

VLOOKUP solo toma un máximo de cuatro argumentos. Si puedes entender estos cuatro, estás listo.

Y dado que VLOOKUP también maneja la mayoría de los casos básicos de búsqueda, rápidamente se ha convertido en la función de Excel más popular.

Lo llamo el Rey de las funciones de Excel.

INDEX / MATCH, por otro lado, es un poco más difícil de usar. Puede que te cuelgue cuando empieces a usarlo, pero para un principiante, BUSCARV es mucho más fácil de explicar y aprender.

Y este no es un juego de suma cero.

Por lo tanto, si es nuevo en el mundo de las búsquedas y no sabe cómo usar BUSCARV, es mejor que lo aprenda.

Tengo una guía detallada sobre el uso de BUSCARV en Excel (con muchos ejemplos)

Mi intención en este artículo no es lanzar dos funciones asombrosas entre sí. Quería mostrarte el poder del combo INDEX MATCH y todas las grandes cosas que puede hacer.

Espero que hayas encontrado útil este artículo.

Déjame saber lo que piensas en la sección de comentarios y, en caso de que encuentres algún error en este tutorial, házmelo saber.