COINCIDENCIA DE ÍNDICE de Excel frente a BUSCARV

Función ÍNDICE: sintaxis y uso

La función ÍNDICE de Excel devuelve un valor en una matriz basada en los números de fila y columna que especifique. La sintaxis de la función ÍNDICE es sencilla:

ÍNDICE(matriz, núm_fila, [column_num])

Aquí tienes una explicación muy sencilla de cada parámetro:

• formación – un rango de celdas del que desea devolver un valor.
• número_fila – el número de fila de la matriz desde la que desea devolver un valor. Si se omite, se requiere column_num.
• núm_columna – el número de columna de la matriz desde la que desea devolver un valor. Si se omite, se requiere núm_fila.

Para obtener más información, consulte la función ÍNDICE de Excel.

Y aquí hay un ejemplo de la fórmula ÍNDICE en su forma más simple:

=INDEX(A1:C10,2,3)

La fórmula busca en las celdas A1 a C10 y devuelve un valor de la celda en la segunda fila y la tercera columna, es decir, la celda C2.

Muy fácil, ¿verdad? Sin embargo, cuando se trabaja con datos reales, casi nunca se sabe qué fila y columna desea, ahí es donde la función COINCIDIR resulta útil.

Función MATCH: sintaxis y uso

La función COINCIDIR de Excel busca un valor de búsqueda en un rango de celdas y devuelve el posición relativa de ese valor en el rango.

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

COINCIDIR(valor_buscado, matriz_buscada, [match_type])

• valor de búsqueda – el valor numérico o de texto que estás buscando.
• matriz_buscada – una variedad de celdas que se están buscando.
• tipo de concordancia – especifica si se debe devolver una coincidencia exacta o la coincidencia más cercana:
  • 1 u omitido: busca el valor más grande que sea menor o igual que el valor de búsqueda. Requiere ordenar la matriz de búsqueda en orden ascendente.
  • 0: encuentra el primer valor que es exactamente igual al valor de búsqueda. En la combinación ÍNDICE / COINCIDIR, casi siempre necesita una coincidencia exacta, por lo que establece el tercer argumento de su función COINCIDIR en 0.
  • -1: busca el valor más pequeño que sea mayor o igual a valor_buscado. Requiere ordenar la matriz de búsqueda en orden descendente.

Por ejemplo, si el rango B1:B3 contiene los valores “Nueva York”, “París”, “Londres”, la siguiente fórmula devuelve el número 3, porque “Londres” es la tercera entrada del rango:

=MATCH("London",B1:B3,0)

Para obtener más información, consulte la función COINCIDIR de Excel.

A primera vista, la utilidad de la función MATCH puede parecer cuestionable. ¿A quién le importa la posición de un valor en un rango? Lo que sí queremos saber es el valor en sí.

Permítame recordarle que la posición relativa del valor de búsqueda (es decir, números de fila y columna) es exactamente lo que necesita proporcionar al número_fila y núm_columna argumentos de la función ÍNDICE. Como recordará, Excel INDEX puede encontrar el valor en la unión de una fila y columna determinadas, pero no puede determinar exactamente qué fila y columna desea.

4 razones principales para usar INDEX MATCH en lugar de BUSCARV

1. Búsqueda de derecha a izquierda. Como sabe cualquier usuario educado, BUSCARV no puede mirar hacia la izquierda, lo que significa que su valor de búsqueda siempre debe residir en la columna más a la izquierda de la tabla. ¡INDEX MATCH puede realizar búsquedas por la izquierda con facilidad! El siguiente ejemplo lo muestra en acción: Cómo buscar en V un valor a la izquierda en Excel.
2. Inserte o elimine columnas de forma segura. Las fórmulas BUSCARV se rompen o arrojan resultados incorrectos cuando se elimina o agrega una nueva columna a una tabla de búsqueda porque la sintaxis de BUSCARV requiere especificar el número de índice de la columna de la que desea extraer los datos. Naturalmente, cuando agrega o elimina columnas, el número de índice cambia.

   Con INDEX MATCH, usted especifica el rango de columnas de retorno, no un número de índice. Como resultado, puede insertar y eliminar tantas columnas como desee sin preocuparse por actualizar cada fórmula asociada.

3. No hay límite para el tamaño de un valor de búsqueda. Cuando utilice la función BUSCARV, la longitud total de sus criterios de búsqueda no puede exceder los 255 caracteres; de lo contrario, terminará teniendo el #¡VALOR! error. Entonces, si su conjunto de datos contiene cadenas largas, INDEX MATCH es la única solución que funciona.
4. Mayor velocidad de procesamiento. Si sus tablas son relativamente pequeñas, apenas habrá una diferencia significativa en el rendimiento de Excel. Pero si sus hojas de cálculo contienen cientos o miles de filas y, en consecuencia, cientos o miles de fórmulas, MATCH INDEX funcionará mucho más rápido que BUSCARV porque Excel tendrá que procesar solo las columnas de búsqueda y devolución en lugar de toda la matriz de la tabla.

   El impacto de BUSCARV en el rendimiento de Excel puede ser especialmente notable si su libro contiene fórmulas de matriz complejas como BUSCARV y SUMA. El punto es que verificar cada valor en la matriz requiere una llamada separada a la función BUSCARV. Por lo tanto, cuantos más valores contenga su matriz y más fórmulas de matriz tenga en un libro, más lento funcionará Excel.

Fórmula INDEX MATCH para buscar de derecha a izquierda

Como ya se mencionó, BUSCARV no puede mirar a su izquierda. Entonces, a menos que sus valores de búsqueda estén en la columna más a la izquierda, no hay posibilidad de que una fórmula de Vlookup le brinde el resultado que desea. La función INDEX MATCH en Excel es más versátil y realmente no le importa dónde se encuentran las columnas de búsqueda y retorno.

Para este ejemplo, agregaremos la columna Clasificación a la izquierda de nuestra tabla de muestra e intentaremos averiguar cómo se clasifica la capital rusa, Moscú, en términos de población.

Con el valor de búsqueda en G1, use la siguiente fórmula para buscar en C2:C10 y devolver un valor correspondiente de A2:A10:

=INDEX(A2:A10,MATCH(G1,C2:C10,0))

INDEX MATCH MATCH para buscar en filas y columnas

En los ejemplos anteriores, utilizamos INDEX MATCH como reemplazo de la BUSCARV clásica para devolver un valor de un rango predefinido de una columna. Pero, ¿qué sucede si necesita buscar en varias filas y columnas? En otras palabras, ¿qué pasa si desea realizar el llamado matriz o bidireccional ¿buscar?

Esto puede parecer complicado, pero la fórmula es muy similar a la función básica COINCIDENCIA DE ÍNDICE de Excel, con solo una diferencia. ¿Adivina qué?

Simplemente, use dos funciones COINCIDIR: una para obtener un número de fila y la otra para obtener un número de columna. Y felicito a los que habéis acertado 🙂

ÍNDICE (matriz, COINCIDIR (valor de búsqueda virtual, columna para mirar hacia arriba0), COINCIDIR (valor de búsqueda, fila para mirar hacia arriba0))

Y ahora, eche un vistazo a la siguiente tabla y creemos una fórmula INDEX MATCH MATCH para encontrar la población (en millones) de un país determinado para un año determinado.

Con el país de destino en G1 (valor de búsquedav) y el año de destino en G2 (valor de búsquedah), la fórmula toma esta forma:

=INDEX(B2:D11, MATCH(G1,A2:A11,0), MATCH(G2,B1:D1,0))

Excel INDEX MATCH para buscar múltiples criterios

Si tuvo la oportunidad de leer nuestro tutorial BUSCARV de Excel, probablemente ya haya probado una fórmula para BUSCARV con múltiples criterios. Sin embargo, una limitación importante de ese enfoque es la necesidad de agregar una columna auxiliar. La buena noticia es que la función COINCIDENCIA DE ÍNDICE de Excel también puede buscar con dos o más criterios, ¡sin modificar ni reestructurar los datos de origen!

Aquí está la fórmula genérica INDEX MATCH con múltiples criterios:

{=ÍNDICE(rango_retornoCOINCIDIR(1, (criterios1=rango1) * (criterios2=rango2), 0))}

En la siguiente tabla de ejemplo, supongamos que desea encontrar la cantidad según dos criterios: Cliente y Producto.

La siguiente fórmula INDEX MATCH funciona de maravilla:

=INDEX(C2:C10, MATCH(1, (F1=A2:A10) * (F2=B2:B10), 0))

Donde C2:C10 es el rango para devolver un valor, F1 es el criterio1, A2:A10 es el rango para comparar con el criterio1, F2 es el criterio 2 y B2:B10 es el rango para comparar con el criterio2.

Recuerde ingresar la fórmula correctamente presionando Ctrl + Shift + Enter, y Excel automáticamente la incluirá entre llaves como se muestra en la captura de pantalla:

Si prefiere no utilizar fórmulas de matriz en sus hojas de trabajo, agregue una función ÍNDICE más a la fórmula y complétela con un clic habitual de Enter:

COINCIDENCIA DE ÍNDICE de Excel con PROMEDIO, MÁXIMO, MIN

Microsoft Excel tiene funciones especiales para encontrar un valor mínimo, máximo y promedio en un rango. Pero, ¿qué sucede si necesita obtener un valor de otra celda asociada con esos valores? En este caso, utilice la función MAX, MIN o PROMEDIO junto con INDEX MATCH.