COINCIDENCIA DE ÍNDICE de Excel frente a BUSCARV

En el mundo del análisis de datos, Excel se ha convertido en una herramienta indispensable para millones de personas en todo el mundo. Dentro de sus numerosas funciones, el índice de coincidencia y la fórmula BUSCARV son dos de las más utilizadas. Pero, ¿cuál es la diferencia entre ellos y cuál debemos elegir en cada situación? En este artículo, exploraremos en profundidad la coincidencia de índice de Excel frente a BUSCARV, revelando sus ventajas y desventajas y proporcionando ejemplos prácticos para que puedas tomar una decisión informada al utilizar estas funciones en tus análisis de datos. ¡Sigue leyendo y descubre cómo maximizar el potencial de Excel en tus proyectos!

Este tutorial muestra cómo usar ÍNDICE y COINCIDIR en Excel y cómo es mejor que BUSCARV.

En un par de artículos recientes, hicimos un gran esfuerzo para explicar los conceptos básicos de la función BUSCARV a los principiantes y proporcionar ejemplos de fórmulas BUSCARV más complejos a los usuarios avanzados. Y ahora, intentaré, si no disuadirte de usar BUSCARV, al menos mostrarte una forma alternativa de realizar una búsqueda vertical en Excel.

«¿Para qué necesito eso?» te preguntarás. Porque BUSCARV tiene numerosas limitaciones que pueden impedir que obtengas el resultado deseado en muchas situaciones. Por otro lado, la combinación INDEX MATCH es más flexible y tiene muchas características increíbles que la hacen superior a BUSCARV en muchos aspectos.
COINCIDENCIA DE ÍNDICE de Excel frente a BUSCARV

Funciones ÍNDICE y COINCIDIR de Excel: conceptos básicos

Dado que el objetivo de este tutorial es demostrar una forma alternativa de realizar una búsqueda virtual en Excel utilizando una combinación de funciones ÍNDICE y COINCIDIR, no nos detendremos mucho en su sintaxis y usos. Cubriremos solo lo mínimo necesario para comprender la idea general y luego analizaremos en profundidad ejemplos de fórmulas que revelan todas las ventajas de usar INDEX MATCH en lugar de 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.

Cómo utilizar la función COINCIDENCIA DE ÍNDICE en Excel

Ahora que conoce los conceptos básicos, creo que ya ha empezado a tener sentido cómo funcionan juntos MATCH e INDEX. En pocas palabras, INDEX encuentra el valor de búsqueda por números de columna y fila, y MATCH proporciona esos números. ¡Eso es todo!

Para la búsqueda vertical, use la función COINCIDIR solo para determinar el número de fila y proporcionar el rango de columnas directamente a ÍNDICE:

ÍNDICE (columna para devolver un valor deFÓSFORO (valor de búsqueda, columna para mirar hacia arriba0))

¿Aún tienes dificultades para resolverlo? Quizás sea más fácil de entender con un ejemplo. Supongamos que tiene una lista de capitales nacionales y su población:
COINCIDENCIA DE ÍNDICE de Excel frente a BUSCARV

Para encontrar la población de una determinada capital, digamos la capital de Japón, utilice la siguiente fórmula INDEX MATCH:

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

Ahora, analicemos qué hace realmente cada componente de esta fórmula:

  • La función COINCIDIR busca el valor de búsqueda «Japón» en el rango A2:A10 y devuelve el número 3, porque «Japón» ocupa el tercer lugar en la matriz de búsqueda.
  • El número de fila va directamente al número_fila argumento de INDEX que le indica que devuelva un valor de esa fila.

Entonces, la fórmula anterior se convierte en un ÍNDICE simple (C2:C,3) que dice buscar en las celdas C2 a C10 y extraer el valor de las 3tercero celda en ese rango, es decir, C4 porque comenzamos a contar desde la segunda fila.

¿No quieres codificar la ciudad en la fórmula? Ingréselo en alguna celda, digamos F1, proporcione la referencia de la celda a MATCH y obtendrá una fórmula de búsqueda dinámica:

=INDEX(C2:C10, MATCH(F1,A2:A10,0))
COINCIDENCIA DE ÍNDICE de Excel frente a BUSCARV

¡Nota IMPORTANTE! El número de filas en el formación El argumento de INDEX debe coincidir con el número de filas en el matriz_buscada argumento de COINCIDIR; de lo contrario, la fórmula producirá un resultado incorrecto.

Espera, espera… ¿por qué no usamos simplemente la siguiente fórmula Vlookup? ¿Cuál es el punto de perder el tiempo tratando de descubrir los giros arcanos de Excel MATCH INDEX?

=VLOOKUP(F1, A2:C10, 3, FALSE)

En este caso, no tiene ningún sentido 🙂 Este ejemplo simple es solo para fines de demostración, para que pueda tener una idea de cómo funcionan juntas las funciones ÍNDICE y COINCIDIR. Otros ejemplos que siguen a continuación le mostrarán el poder real de esta combinación que hace frente fácilmente a muchos escenarios complejos cuando BUSCARV falla.

COINCIDENCIA DE ÍNDICE vs. BUSCARV

Al decidir qué función utilizar para las búsquedas verticales, la mayoría de los gurús de Excel coinciden en que INDEX MATCH es mucho mejor que BUSCARV. Sin embargo, muchas personas todavía se quedan con BUSCARV, en primer lugar, porque es más simple y, en segundo lugar, porque no comprenden completamente todos los beneficios de usar la fórmula INDEX COINCIDENCIA en Excel. Sin esa comprensión, nadie está dispuesto a invertir su tiempo en aprender una sintaxis más compleja.

A continuación, señalaré las ventajas clave de MATCH INDEX sobre BUSCARV, y usted decidirá si es una valiosa adición a su arsenal de Excel.

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.

COINCIDENCIA DE ÍNDICE de Excel: ejemplos de fórmulas

Conociendo las razones para aprender la función MATCH INDEX, vayamos a la parte más interesante y veamos cómo puedes aplicar los conocimientos teóricos en la práctica.

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))
COINCIDENCIA DE ÍNDICE de Excel frente a BUSCARV

Consejo. Si planea usar su fórmula INDEX MATCH para más de una celda, asegúrese de bloquear ambos rangos con referencias de celda absolutas (como $A$2:$A$10 y $C$2:4C$10) para que no se distorsionen cuando copiando la fórmula.

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))
COINCIDENCIA DE ÍNDICE de Excel frente a BUSCARV

Cómo funciona esta fórmula

Siempre que necesite comprender una fórmula compleja de Excel, divídala en partes más pequeñas y vea qué hace cada función individual:

MATCH(G1,A2:A11,0) – busca en A2:A11 el valor de la celda G1 («China») y devuelve su posición, que es 2.

MATCH(G2,B1:D1,0)) – busca en B1:D1 para obtener la posición del valor en la celda G2 («2015»), que es 3.

Los números de fila y columna anteriores van a los argumentos correspondientes de la función ÍNDICE:

INDEX(B2:D11, 2, 3)

Como resultado, obtienes un valor en la intersección de la segunda fila y la tercera columna en el rango B2:D11, que es el valor de la celda D3. ¿Fácil? ¡Sí!

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))}

Nota. Esta es una fórmula matricial que debe completarse con el atajo Ctrl + Shift + Enter.

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:
COINCIDENCIA DE ÍNDICE de Excel frente a BUSCARV

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 frente a BUSCARV

Cómo funcionan estas fórmulas

Las fórmulas utilizan el mismo enfoque que la función INDEX MATCH básica que examina una sola columna. Para evaluar múltiples criterios, cree dos o más matrices de valores VERDADEROS y FALDOS que representen coincidencias y no coincidencias para cada criterio individual, y luego multiplique los elementos correspondientes de estas matrices. La operación de multiplicación convierte VERDADERO y FALSO en 1 y 0, respectivamente, y produce una matriz donde los 1 corresponden a filas que coinciden con todos los criterios. La función COINCIDIR con un valor de búsqueda de 1 encuentra el primer «1» en la matriz y pasa su posición a ÍNDICE, que devuelve un valor en esta fila de la columna especificada.

La fórmula sin matriz se basa en la capacidad de la función ÍNDICE para manejar matrices de forma nativa. El segundo ÍNDICE está configurado con 0. número_fila para que pase toda la matriz de columnas a MATCH.

Ésta es una explicación de alto nivel de la lógica de la fórmula. Para obtener detalles completos, consulte COINCIDENCIA DE ÍNDICE de Excel con múltiples criterios.

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.

PARTIDO DE ÍNDICE con MAX

Para encontrar el valor más grande en la columna D y devolver un valor de la columna C en la misma fila, use esta fórmula:

=INDEX(C2:C10, MATCH(MAX(D2:D10), D2:D10, 0))

PARTIDO DE ÍNDICE con MIN

Para ubicar el valor más pequeño en la columna D y extraer un valor asociado de la columna C, use este:

=INDEX(C2:C10, MATCH(MIN(D2:D10), D2:D10, 0))

COINCIDENCIA DE ÍNDICE con PROMEDIO

Para calcular el valor más cercano al promedio en D2:D10 y obtener el valor correspondiente de la columna C, esta es la fórmula a utilizar:

=INDEX(C2:C10, MATCH(AVERAGE(D2:D10), D2:D10, -1 ))

Dependiendo de cómo estén organizados sus datos, proporcione 1 o -1 al tercer argumento (match_type) de la función COINCIDIR:

  • Si su columna de búsqueda (columna D en nuestro caso) está ordenada ascendiendopon 1. La fórmula calculará el valor más grande que es menos que o igual al valor medio.
  • Si su columna de búsqueda está ordenada descendiendo, ingrese -1. La fórmula calculará el valor más pequeño que es mas grande que o igual al valor medio.
  • Si su matriz de búsqueda contiene un valor exactamente igual al promedio, puede ingresar 0 para una coincidencia exacta. No se requiere clasificación.

En nuestro ejemplo, las poblaciones de la columna D están ordenadas en orden descendente, por lo que utilizamos -1 para el tipo de coincidencia. Como resultado, obtenemos «Tokio», ya que su población (13.189.000) es la que más se acerca y es mayor que el promedio (12.269.006).
COINCIDENCIA DE ÍNDICE de Excel frente a BUSCARV

Quizás tengas curiosidad por saber que BUSCARV también puede realizar este tipo de cálculos, pero como una fórmula matricial: BUSCARV con PROMEDIO, MÁXIMO, MIN.

Usando INDEX MATCH con IFNA / IFERROR

Como probablemente habrás notado, si una fórmula INDEX MATCH en Excel no puede encontrar un valor de búsqueda, produce un error #N/A. Si desea reemplazar la notación de error estándar con algo más significativo, ajuste su fórmula INDEX MATCH en la función IFNA. Por ejemplo:

=IFNA(INDEX(C2:C10, MATCH(F1,A2:A10,0)), "No match is found")

Y ahora, si alguien ingresa una tabla de búsqueda que no existe en el rango de búsqueda, la fórmula informará explícitamente al usuario que no se encuentra ninguna coincidencia:
COINCIDENCIA DE ÍNDICE de Excel frente a BUSCARV

Si desea detectar todos los errores, no solo #N/A, use la función SIERROR en lugar de IFNA:

=IFERROR(INDEX(C2:C10, MATCH(F1,A2:A10,0)), "Oops, something went wrong!")

Tenga en cuenta que en muchas situaciones puede resultar imprudente disfrazar todos los errores porque le alertan sobre posibles fallos en su fórmula.

Así es como se usa INDEX y COINCIDIR en Excel. Espero que nuestros ejemplos de fórmulas te resulten útiles y espero verte en nuestro blog la próxima semana.

Libro de práctica para descargar.

Ejemplos de COINCIDENCIA DE ÍNDICE de Excel (archivo .xlsx)

Usted también podría estar interesado en

Error 403 The request cannot be completed because you have exceeded your quota. : quotaExceeded






COINCIDENCIA DE ÍNDICE de Excel frente a BUSCARV – Preguntas frecuentes

COINCIDENCIA DE ÍNDICE de Excel frente a BUSCARV – Preguntas frecuentes

Si trabajas con Microsoft Excel, es probable que hayas escuchado sobre las funciones COINCIDENCIA de ÍNDICE y BUSCARV. Estas dos funciones son ampliamente utilizadas para buscar y recuperar datos en una hoja de cálculo. A continuación, te presentamos algunas de las preguntas frecuentes sobre estas funciones y sus diferencias:

1. ¿Qué es la función COINCIDENCIA de ÍNDICE?

La función COINCIDENCIA de ÍNDICE en Excel es una combinación de dos funciones: COINCIDENCIA e ÍNDICE. Permite buscar un valor en una matriz y devolver un valor correspondiente en otra posición dentro de esa matriz. La función COINCIDENCIA busca la posición del valor especificado y la función ÍNDICE devuelve el valor en esa posición.

2. ¿En qué se diferencia la función BUSCARV de la función COINCIDENCIA de ÍNDICE?

La principal diferencia entre la función BUSCARV y la función COINCIDENCIA de ÍNDICE es que BUSCARV busca un valor en la primera columna de una tabla y devuelve un valor de una columna especificada en esa misma fila. Por otro lado, COINCIDENCIA de ÍNDICE es más flexible, ya que puedes buscar un valor en cualquier posición dentro de una matriz y devolver un valor de cualquier posición relacionada en esa misma matriz.

3. ¿Cuándo debo utilizar la función COINCIDENCIA de ÍNDICE?

La función COINCIDENCIA de ÍNDICE es útil cuando necesitas buscar un valor en una tabla no ordenada y devolver un valor relacionado en esa misma tabla, incluso si no se encuentra en la misma fila o columna. Por ejemplo, si tienes una tabla con nombres de empleados y sus salarios, y deseas buscar un salario específico, puedes utilizar la función COINCIDENCIA de ÍNDICE para encontrar el nombre del empleado asociado.

4. ¿Y cuándo debo utilizar la función BUSCARV?

La función BUSCARV es útil cuando necesitas buscar un valor en la primera columna de una tabla ordenada y devolver un valor relacionado de una columna especificada en esa misma fila. Es comúnmente utilizada para buscar información en una tabla de datos, donde la primera columna contiene los valores a buscar.

5. ¿Existen limitaciones al utilizar estas funciones?

Sí, es importante tener en cuenta algunas limitaciones al utilizar estas funciones:

  1. La función COINCIDENCIA de ÍNDICE solo devuelve el primer valor coincidente encontrado en la matriz. Si hay más de una coincidencia, solo se devuelve la primera.
  2. La función BUSCARV solo busca valores en la primera columna de una tabla ordenada. Si deseas buscar valores en otras columnas, debes utilizar otras funciones como BUSCARH o ÍNDICE+COINCIDIR.
  3. Ambas funciones son sensibles a mayúsculas y minúsculas. Asegúrate de que los valores coincidan exactamente para obtener resultados precisos.

Esperamos que estas preguntas frecuentes te hayan proporcionado una mejor comprensión de las funciones COINCIDENCIA de ÍNDICE y BUSCARV en Excel. Si deseas aprender más sobre estas funciones, puedes visitar los siguientes enlaces:


Deja un comentario