Excel

Cómo usar INDEX y MATCH

How Use Index Match

INDEX and MATCH es la herramienta más popular en Excel para realizar búsquedas más avanzadas. Esto se debe a que INDEX and MATCH es increíblemente flexible: puede realizar búsquedas horizontales y verticales, búsquedas bidireccionales, búsquedas a la izquierda, búsquedas que distinguen entre mayúsculas y minúsculas e incluso búsquedas basadas en múltiples criterios. Si desea mejorar sus habilidades en Excel, INDICE y MATCH deben estar en su lista.





Este artículo explica en términos simples cómo usar ÍNDICE y COINCIDIR juntos para realizar búsquedas. Se necesita un enfoque paso a paso, primero explicando ÍNDICE, luego COINCIDIR, luego mostrándole cómo combinar las dos funciones para crear una búsqueda dinámica bidireccional. Hay ejemplos más avanzados más abajo en la página.

Función INDICE | Función COINCIDIR | ÍNDICE y PARTIDO | Búsqueda bidireccional | Búsqueda izquierda | Distingue mayúsculas y minúsculas | Partido más cercano | Múltiples criterios | Más ejemplos





La función INDEX

La función INDICE en Excel es increíblemente flexible y poderosa, y la encontrará en una gran cantidad de fórmulas de Excel, especialmente fórmulas avanzadas. Pero, ¿qué hace realmente INDEX? En pocas palabras, INDEX recupera el valor en una ubicación determinada en un rango. Por ejemplo, digamos que tiene una tabla de planetas en nuestro sistema solar (ver más abajo) y desea obtener el nombre del cuarto planeta, Marte, con una fórmula. Puedes usar ÍNDICE así:

|_+_|

Usando ÍNDICE para obtener el nombre del cuarto planeta
INDICE devuelve el valor de la cuarta fila del rango.



Video: Cómo buscar cosas con INDEX

¿Qué pasa si quieres obtener el diámetro de Marte con INDEX? En ese caso, podemos proporcionar un número de fila y un número de columna, y proporcionar un rango mayor. La fórmula INDICE a continuación utiliza el rango completo de datos en B3: D11, con un número de fila de 4 y un número de columna de 2:

|_+_|

Usando ÍNDICE para obtener el diámetro del cuarto planeta
INDICE recupera el valor en la fila 4, columna 2.

Para resumir, INDICE obtiene un valor en una ubicación determinada en un rango de celdas según la posición numérica. Cuando el rango es unidimensional, solo necesita proporcionar un número de fila. Cuando el rango es bidimensional, deberá proporcionar tanto el número de fila como el de columna.

En este punto, puede estar pensando '¿Y qué? ¿Con qué frecuencia conoce realmente la posición de algo en una hoja de cálculo?

Exactamente correcto. Necesitamos una forma de localizar la posición de las cosas que estamos buscando.

Ingrese a la función COINCIDIR.

La función COINCIDIR

La función COINCIDIR está diseñada para un propósito: encontrar la posición de un elemento en un rango. Por ejemplo, podemos usar MATCH para obtener la posición de la palabra 'melocotón' en esta lista de frutas como esta:

|_+_|

Usando MATCH para encontrar la posición en un rango vertical
MATCH devuelve 3, ya que 'Peach' es el tercer elemento. MATCH no distingue entre mayúsculas y minúsculas.

A MATCH no le importa si un rango es horizontal o vertical, como puede ver a continuación:

|_+_|

Usando MATCH para encontrar la posición en un rango horizontal
El mismo resultado con un rango horizontal, COINCIDIR devuelve 3.

Video: Cómo usar MATCH para coincidencias exactas

Importante: el último argumento de la función COINCIDIR es el tipo de coincidencia. El tipo de concordancia es importante y controla si la concordancia es exacta o aproximada. En muchos casos, querrá usar cero (0) para forzar el comportamiento de coincidencia exacta. El tipo de coincidencia predeterminado es 1, lo que significa coincidencia aproximada, por lo que es importante proporcionar un valor. Ver el Página MATCH para más detalles.

ÍNDICE y COINCIDIR juntos

Ahora que hemos cubierto los conceptos básicos de INDICE y MATCH, ¿cómo combinamos las dos funciones en una sola fórmula? Considere los datos a continuación, una tabla que muestra una lista de vendedores y cifras de ventas mensuales durante tres meses: enero, febrero y marzo.

Ventas por vendedor por mes

Digamos que queremos escribir una fórmula que devuelva el número de ventas de febrero para un vendedor determinado. De la discusión anterior, sabemos que podemos darle a INDEX un número de fila y columna para recuperar un valor. Por ejemplo, para devolver el número de ventas de febrero de Frantz, proporcionamos el rango C3: E11 con una fila 5 y una columna 2:

|_+_|

Pero obviamente no queremos codificar números. En cambio, queremos un dinámica buscar.

¿Cómo haremos eso? La función MATCH, por supuesto. MATCH funcionará perfectamente para encontrar las posiciones que necesitamos. Trabajando paso a paso, dejemos la columna codificada como 2 y hagamos que el número de fila sea dinámico. Aquí está la fórmula revisada, con la función COINCIDIR anidada dentro de ÍNDICE en lugar de 5:

|_+_|

Llevando las cosas un paso más allá, usaremos el valor de H2 en MATCH:

|_+_|

INDICE y COINCIDIR para encontrar las ventas de febrero para cualquier nombre
COINCIDIR encuentra 'Frantz' y devuelve 5 a ÍNDICE para la fila.

Para resumir:

  1. ÍNDICE necesita posiciones numéricas.
  2. MATCH encuentra esas posiciones.
  3. PARTIDO es anidado dentro de ÍNDICE.

Abordemos ahora el número de columna.

Búsqueda bidireccional con INDEX y MATCH

Arriba, usamos la función COINCIDIR para encontrar el número de fila dinámicamente, pero codificamos el número de columna. ¿Cómo podemos hacer que la fórmula sea completamente dinámica, de modo que podamos devolver las ventas de cualquier vendedor en un mes determinado? El truco consiste en usar MATCH dos veces: una para obtener una posición de fila y otra para obtener una posición de columna.

De los ejemplos anteriores, sabemos que MATCH funciona bien con matrices horizontales y verticales. Eso significa que podemos encontrar fácilmente la posición de un mes determinado con MATCH. Por ejemplo, esta fórmula devuelve la posición de marzo, que es 3:

|_+_|

Pero, por supuesto, no queremos codificar alguna valores, así que actualice la hoja de trabajo para permitir la entrada de un nombre de mes, y usemos COINCIDIR para encontrar el número de columna que necesitamos. La siguiente pantalla muestra el resultado:

Búsqueda dinámica con INDEX y MATCH
Una búsqueda bidireccional completamente dinámica con INDEX y MATCH.

|_+_|

La primera fórmula COINCIDIR devuelve 5 a ÍNDICE como número de fila, la segunda fórmula COINCIDIR devuelve 3 a ÍNDICE como número de columna. Una vez que se ejecuta MATCH, la fórmula se simplifica a:

|_+_|

e INDEX devuelve correctamente $ 10,525, el número de ventas de Frantz en marzo.

Nota: podrías usar Validación de datos para crear menús desplegables para seleccionar vendedor y mes.

Video: Cómo hacer una búsqueda bidireccional con ÍNDICE y COINCIDIR

Video: Cómo depurar una fórmula con F9 (para ver los valores de retorno COINCIDIR)

Búsqueda izquierda

Una de las ventajas clave de ÍNDICE y COINCIDIR sobre la función BUSCARV es la capacidad de realizar una 'búsqueda a la izquierda'. En pocas palabras, esto solo significa una búsqueda donde la columna de ID está en el Derecha de los valores que desea recuperar, como se ve en el siguiente ejemplo:

Búsqueda izquierda con ÍNDICE y COINCIDIR

Lea una explicación detallada aquí. .

Búsqueda sensible a mayúsculas y minúsculas

Por sí misma, la función COINCIDIR no distingue entre mayúsculas y minúsculas. Sin embargo, usa el Función EXACTA con ÍNDICE y COINCIDIR para realizar una búsqueda que respete mayúsculas y minúsculas, como se muestra a continuación:

Búsqueda sensible a mayúsculas y minúsculas con INDEX y MATCH

Lea una explicación detallada aquí. .

Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter, excepto en Excel 365 .

Partido más cercano

Otro ejemplo que muestra la flexibilidad de INDEX y MATCH es el problema de encontrar el coincidencia más cercana . En el siguiente ejemplo, usamos el Función MIN junto con la Función ABS para crear un valor de búsqueda y una matriz de búsqueda dentro la función COINCIDIR. Esencialmente, usamos MATCH para encontrar la diferencia más pequeña. Luego usamos INDICE para recuperar el viaje asociado de la columna B.

Encuentra la coincidencia más cercana con INDEX y MATCH

Lea una explicación detallada aquí. .

Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter, excepto en Excel 365 .

Búsqueda de varios criterios

Uno de los problemas más complicados de Excel es una búsqueda basada en varios criterios. En otras palabras, una búsqueda que coincide en más de una columna al mismo tiempo. En el siguiente ejemplo, estamos usando ÍNDICE y COINCIDIR y lógica booleana para hacer coincidir en 3 columnas: artículo, color y tamaño:

INDICE y COINCIDIR con múltiples criterios

Lea una explicación detallada aquí. .

Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter, excepto en Excel 365 .

cómo hacer que un número negativo sea positivo en Excel

Más ejemplos de INDICE + MATCH

Aquí hay algunos ejemplos más básicos de INDEX y MATCH en acción, cada uno con una explicación detallada:

Autor Dave Bruns Adjuntos Expediente exceljet index y match.xlsx


^