La verdadera razón por la que INDEX/MATCH es mejor que BUSCARV

Si lee muchos blogs y sitios de Excel, verá surgir un tema común que nos anima a todos a utilizar la combinación de fórmulas INDEX COINCIDENCIA, en lugar de BUSCARV. No voy a estar en desacuerdo con esa recomendación, sin embargo, siento la pregunta "¿Por qué INDEX MATCH es mejor que BUSCARV?" muchas veces no se explora completamente. En mi opinión, las respuestas normalmente proporcionan una visión demasiado dura de BUSCARV y una visión demasiado simple de INDEX MATCH.

Cualquiera que sea el lado en el que te sientes en el debate BUSCARV versus COINCIDENCIA ÍNDICE, en esta publicación quiero mostrarte que ambas fórmulas son significativamente más poderosas de lo que imaginas.

Para empezar, me gustaría equilibrar los equipos. INDEX y MATCH por sí solos no pueden lograr lo que VLOOKUP puede hacer, pero en combinación se vuelven más poderosos. ¿Qué pasaría si hubiera una función que, combinada con BUSCARV, también la hiciera más poderosa? Sería una comparación justa, ¿verdad? Afortunadamente, existe una función de este tipo, la función ELEGIR (sigue leyendo para descubrir cómo). En esta publicación se presentará BUSCARV ELEGIR vs PARTIDO DE ÍNDICE.

Entonces, ahora que tenemos una pelea justa, pongámonos en marcha.

Resumen de funciones

Recordemos lo que hace cada función. Si ya está satisfecho con estos, no dude en pasar a la siguiente sección.

BUSCARV

BUSCARV busca un valor en la columna izquierda de un rango específico y devuelve un resultado de la misma fila de una columna especificada.

La sintaxis de la función es:

=BUSCARV(valor_búsqueda, matriz_tabla, núm_índice_columna, [rango_búsqueda])

• valor_buscado: el valor a buscar en la primera columna
• table_array: el rango de celdas que contienen los datos
• col_index_num: la columna en table_array de la que recuperar el resultado
• [range_lookp]: Un valor de 1, 0, VERDADERO o FALSO. Este argumento es opcional. Sin embargo, si se omite, se asumirá que el valor match_type debe ser 1 o VERDADERO.
  • 1 o VERDADERO = la función devolverá el resultado del valor más grande que sea menor o igual que el valor_buscado. Para calcular correctamente, los valores en lookup_array deben estar en orden ascendente
  • 0 o FALSO = la función devolverá el resultado de la primera coincidencia exacta del valor_buscado. Los valores en lookup_array pueden estar en cualquier orden.

FÓSFORO

La función COINCIDIR encuentra la posición de un elemento dentro de una lista.

La sintaxis de esta función es la siguiente:

=COINCIDIR(valor_buscado, matriz_buscada, [tipo_coincidencia])

• valor_buscado: el valor que se encontrará
• lookup_array: el rango de celdas desde las cuales encontrar el valor de búsqueda.
• [match_type]: un valor de 1, 0 o -1, que determina cómo realizar el cálculo de búsqueda. Este argumento es opcional; si se omite, se asumirá que el valor match_type debe ser 1.
  • 1 = la función devolverá el valor más grande que sea menor o igual al valor_buscado. Para calcular correctamente, los valores en lookup_array deben estar en orden ascendente.
  • -1 = la función devolverá el valor más pequeño que sea mayor o igual al valor de búsqueda. Para calcular correctamente, los valores en lookup_array deben estar en orden descendente.
  • 0 = la función devolverá la primera coincidencia exacta encontrada. Los valores en lookup_array pueden estar en cualquier orden.

ÍNDICE

La función ÍNDICE calcula la referencia a una celda en función de una posición de fila o columna determinada.

ÍNDICE tiene dos formas:

forma de matriz

La sintaxis para la forma de matriz es la siguiente:

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

• matriz: el rango de celdas del cual recuperar una o más celdas.
• row_num: la enésima posición de la fila que se encuentra en la matriz.
• column_num: la enésima posición de la columna que se encuentra en la matriz. El argumento es opcional o se puede utilizar junto con el número de fila.

Cuando la matriz tiene una única dimensión (es decir, entre filas o columnas), el número_fila funciona correctamente en filas o columnas. Sólo cuando la matriz tiene dos dimensiones se requiere [column_num].

Si num_fila o num_columna tiene un valor de 0 o permanece en blanco, se devuelve la fila o columna completa.

El resultado de la función ÍNDICE puede ser un valor o una referencia de celda.

Formulario de referencia

El formulario de referencia permite la selección de celdas no adyacentes.

La sintaxis del formulario de referencia es la siguiente:

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

• referencia: múltiples rangos de celdas, contenidas entre paréntesis y separadas por comas
• [area_num] – un número de índice para determinar de qué rango de celdas contenidas en el argumento de referencia se devuelve el resultado

Los demás argumentos son los mismos que en la forma de matriz.

Desafortunadamente, el formulario de referencia sólo funciona cuando todas las referencias están en una sola hoja. Si necesita hacer referencia a rangos en diferentes hojas, se recomienda ELEGIR (que está fuera del alcance de este artículo).

ELEGIR

Devuelve un valor o rango específico de una lista.

La sintaxis de la función es:

= ELEGIR (núm_índice, Valor 1, [Valor 2...],)

• index_num: especifica qué valor o rango devolver de la lista
• Valor 1: el primer elemento de la lista
• [Valor 2…] – elementos de lista opcionales, pueden haber hasta 254 elementos diferentes en la lista, todos separados por comas.

Desmentir los defectos de BUSCARV

Hay dos defectos comunes atribuidos a BUSCARV:

• Solo puedo buscar hacia la derecha
• El número de columna no se actualiza cuando se eliminan columnas

Ambos defectos son completamente correctos si BUSCARV funciona por sí solo. Pero cuando se combina con ELEGIR podemos mitigar ambos problemas.

BUSCARV puede buscar hacia la izquierda

Lookup_array (es decir, la tabla de la cual BUSCARV recuperará el resultado) suele ser una tabla en el frente de la hoja de cálculo. Pero cuando se combina con la función ELEGIR, podemos crear un lookup_array temporal que solo existe en el punto de cálculo.

La captura de pantalla anterior muestra cómo se compara la combinación BUSCARV ELEGIR con la combinación INDEX MATCH.

Al usar BUSCARV normalmente sería imposible buscar el nombre de una persona para un valor específico, ya que el valor_buscado no está en la columna más a la izquierda. Sin embargo, la función ELEGIR nos permite crear una tabla temporal con las columnas en el orden que deseemos. La fórmula en la celda K4 es:

=BUSCARV(K2,ELEGIR({1,2},C2:C8,A2:A8),2,0)

El orden de los rangos dentro de la función ELEGIR es importante, la columna de búsqueda (Celdas C2:C8) es la primera, seguida de la columna para devolver el resultado (Celdas A2:A8). Al utilizar una matriz de {1,2} como index_num, crea una lookup_array temporal con las columnas en el orden especificado.

Como se muestra en el ejemplo anterior, BUSCARV mira hacia la izquierda, exactamente lo que se afirma que no puede hacer.

BUSCARV puede hacer frente a la eliminación de columnas

Los rangos utilizados dentro de la función ELEGIR no se basan en su posición en la hoja de trabajo, sino en el orden en el lookup_array temporal. Cuando se utiliza de esta manera, es posible eliminar columnas sin que el cálculo arroje un resultado incorrecto.

El cálculo antes de eliminar columnas.

La captura de pantalla anterior muestra el resultado de la fórmula antes de eliminar columnas.

El cálculo después de eliminar columnas.

La captura de pantalla anterior muestra la fórmula después de eliminar columnas (sin otros cambios). Al utilizar la función ELEGIR dentro de BUSCARV, el cálculo es correcto antes y después de eliminar una columna.

VLOOUP nuevamente está logrando algo más que, según afirma, no puede hacer.

Sin embargo …

He demostrado que BUSCARV, cuando se le brinda el compañero de equipo adecuado, puede lograr los mismos resultados que INDEX MATCH para cálculos de búsqueda básicos.

Sin embargo… esto no la convierte en la mejor opción; INDEX MATCH es la combinación superior. Se afirma que INDEX MATCH es más difícil de entender; si eso es cierto, los usuarios sin duda tendrán dificultades con BUSCARV ELEGIR.

La verdadera razón por la que INDEX MATCH es mejor

Los beneficios reales de INDEX MATCH van mucho más allá que los de una alternativa a BUSCARV. Es una combinación de fórmulas que a veces pueden conseguir lo que parece imposible. Deja que te enseñe…

Puede ser significativamente más rápido

En el pasado, realicé pruebas de velocidad entre INDEX MATCH y VLOOKUP. Hay muy poca diferencia entre ellos. Pero nadie dijo nunca que había que usar INDEX y MATCH juntos dentro de la misma celda.

La parte más lenta de cualquier fórmula de búsqueda es buscar el valor coincidente. COINCIDIR y BUSCARV realizan esta actividad de búsqueda. INDEX, en comparación, recupera un valor de un rango de celdas y es excepcionalmente rápido.

Reutilizar el mismo cálculo MATCH proporciona tiempos de cálculo más rápidos, ya que la sección más lenta del cálculo se realiza con menos frecuencia.

Mire la captura de pantalla a continuación. Observe cómo las fórmulas de las celdas L4 y M4 hacen referencia al resultado de la función COINCIDIR en la celda K4. Los cálculos de BUSCARV equivalentes se encuentran en las celdas L7 y M7.

El cálculo de COINCIDENCIA más lento solo se realiza una vez, mientras que la función ÍNDICE rápida se realiza dos veces.

Como BUSCARV es una función única, debe volver a realizar la búsqueda cada vez que se utiliza la función. Esto hace que BUSCARV sea mucho más lento para devolver varias columnas de la misma tabla.

Compensación de búsqueda

Si queremos encontrar un valor que esté visualmente por encima o por debajo del valor de búsqueda, ¿qué funciones podríamos usar? Para INDEX MATCH esto no es un problema, simplemente agregue o reste un número de compensación al resultado de la función MATCH.

La fórmula en la celda K4 es:

=ÍNDICE(C2:C8,COINCIDIR(K2,A2:A8,0)+1)

Observe que se ha agregado 1 al resultado de MATCH, INDEX devolverá el resultado una celda más abajo. Utilice menos 1 para devolver la celda de arriba.

¿Podemos lograr el mismo resultado con BUSCARV ELEGIR? Sí, si desalineamos los dos rangos dentro de la función ELEGIR.

La fórmula en la celda K6 es:

=BUSCARV(K2,ELEGIR({1,2},A2:A8,C3:C8),2,0)

Observe cómo el primer rango (celdas A2 – A8) comienza en la fila 2, mientras que el segundo rango (celdas C3-C8) comienza en la fila 3. Si bien esto es posible, no es una gran solución.

INDEX MATCH puede realizar 5 tipos de búsqueda

El último argumento de BUSCARV y COINCIDIR indica el tipo de búsqueda a realizar.

Coincidencia exacta

BUSCARV utiliza 0 o FALSO como último argumento para realizar una coincidencia exacta.

MATCH utiliza 0 como último argumento para realizar una coincidencia exacta.

En la siguiente captura de pantalla se muestran ejemplos de ambos:

La fórmula en la celda K5 es:

=BUSCARV(K2,A2:H8,3,0)

La fórmula en la celda K6 es:

=ÍNDICE(C2:C8,COINCIDIR(K2,A2:A8,0))

Si el elemento que se busca no está en la lista, ambas fórmulas devolverán un #N/A.

Coincidencia aproximada: lista ordenada ascendente

BUSCARV utiliza 1 o VERDADERO como último argumento para realizar una coincidencia aproximada en una lista ordenada ascendente.

MATCH utiliza 1 como último argumento para realizar una coincidencia aproximada en una lista ordenada ascendente.

Si el elemento no está en la lista, ambas funciones devolverán el valor anterior al valor buscado.

En el ejemplo (ver arriba), se busca a Kate con una coincidencia aproximada, pero como Kate no está en las celdas A2-A8, el resultado devuelto es para Laurie, que es el valor anterior.

La fórmula en la celda K8 es:

=BUSCARV(K3,A2:H8,3,1)

La fórmula en la celda K9 es:

=ÍNDICE(C2:C8,COINCIDIR(K3,A2:A8,1))

Coincidencia aproximada: lista ordenada descendente

BUSCARV no tiene una opción para calcular en orden descendente. Necesitaría columnas auxiliares o remodelación de datos.

MATCH utiliza -1 como último argumento para realizar una coincidencia aproximada en una lista ordenada descendente.

La fórmula en la celda K5 es:

=ÍNDICE(C2:C8,COINCIDIR(K3,A2:A8,-1))

Si el elemento no está en la lista MATCH devolverá el valor anterior al valor buscado.

Tipos de búsqueda adicionales

Al combinar el concepto de desplazamiento de búsqueda presentado anteriormente con las variantes ascendentes o descendentes, se crean tipos de búsqueda adicionales. Normalmente, las listas ascendentes devolverían el valor anterior, pero agregue una y siempre devolverá el valor después. Del mismo modo, las listas descendentes se pueden ampliar utilizando el mismo concepto de desplazamiento.

Con los tres tipos de coincidencias y los dos adicionales del desplazamiento de búsqueda, son cinco en total. Esto se compara con BUSCARV, que sólo tiene dos.

INDEX MATCH funciona en un rango horizontal

INDEX MATCH, cuando se usa con datos unidimensionales, no le importa si los datos están organizados vertical u horizontalmente. Mientras que BUSCARV, por definición, sólo funciona con datos dispuestos verticalmente.

Ah... pero ¿qué pasa con BUSCARH? Te oigo decir. Sí, es cierto, pero es una función diferente, por lo que tener que recordar qué función usar dependiendo de la orientación de los datos no es lo ideal.

Un lector, David Newell, me señaló que es posible utilizar la combinación BUSCARH/ELEGIR para buscar un valor anterior. La matriz está separada por un punto y coma, en lugar de una coma. A continuación se muestra un ejemplo de cómo se formaría la combinación BUSCARH/ELEGIR.

=BUSCARH("Tom",ELEGIR({1;2},A2:H2,A1:H1),2,0)

Si bien la combinación BUSCARH/ELEGIR es una opción, rara vez la verás utilizada.

INDEX MATCH puede buscar una fila o columna completa

BUSCARV solo puede devolver un valor único. Pero INDEX MATCH puede devolver una columna completa de valor. El siguiente ejemplo muestra que es posible buscar el valor total en todas las columnas sin la necesidad de una columna total.

La celda K4 en la captura de pantalla anterior muestra el total de enero a junio para Jackie. La fórmula en la celda K4 es:

=SUMA(ÍNDICE(C2:H8,COINCIDENCIA(K2,A2:A8,0),0))

Las dos cosas que me gustaría que notaras son:

• El rango ÍNDICE hace referencia a todas las celdas C2 - H8, en lugar de a una sola fila o columna.
• Un ",0" adicional (o simplemente un "," (coma) adicional) le indica a ÍNDICE que devuelva la fila completa de datos.

Se devuelven todos los valores (29, 32, 42, 38, 39 y 42) de la fila 6. No es evidente que esto haya sucedido ya que Excel solo mostrará el primer resultado. Al incluir INDEX MATCH dentro de la función SUMA, sumará todos los valores.

=SUMA(ÍNDICE(C2:H8,COINCIDENCIA(K2,A2:A8,0),0))

El resultado de la fórmula es 212, lo que demuestra que se ha devuelto toda la fila de datos, en lugar de una sola celda.

INDEX MATCH puede crear un rango dinámico

Vimos en el último ejemplo que INDEX MATCH podía buscar una fila o columna completa. Pero, ¿qué pasa si solo queremos buscar un rango específico dentro de las filas y columnas? En la captura de pantalla siguiente, dos fórmulas INDEX MATCH están separadas por dos puntos (:). Esto crea un rango que comienza en la celda del primer resultado y termina en la celda del segundo resultado.

La fórmula en la celda K5 es:

=SUMA(ÍNDICE(C2:C8,COINCIDENCIA(K2,A2:A8,0)):ÍNDICE(C2:C8,COINCIDENCIA(K3,A2:A8,0)))

El resultado de la primera COINCIDENCIA DE ÍNDICE es la celda C3, el resultado de la segunda COINCIDENCIA DE ÍNDICE es C6. Cuando están separados por dos puntos, se crea el rango C3:C6.

Se agregó la función SUMA para mostrar que se devuelve un rango, ya que los valores 29, 42, 26 y 29 (de las celdas C3-C6) suman el resultado mostrado de 126.

Puede realizar fórmulas de matriz sin Ctrl + Shift + Enter

Al crear fórmulas de matriz, como la coincidencia de múltiples criterios, tanto INDEX MATCH como VLOOKUP CHOOSE pueden lograr el resultado. La siguiente captura de pantalla muestra el resultado al combinar el nombre y apellido como valor_buscado.

La fórmula en la celda K5 es:

{=ÍNDICE(C2:C8,COINCIDIR(K2"-"K3,A2:A8"-"B2:B8,0))}

La fórmula en la celda K7 es:

{=BUSCARV(K2"-"K3,ELEGIR({1,2},A2:A8"-"B2:B8,C2:C8),2,0)}

Ambas son fórmulas de matriz y solo se calcularán correctamente si se presiona Ctrl + Shift + Enter al ingresar la fórmula en la celda o barra de fórmulas. No agregue las llaves ( { } ), Excel las agregará por sí solo.

Pero aquí está la magia. Al agregar otro ÍNDICE dentro de COINCIDIR, es posible evitar la necesidad de Ctrl + Shift + Enter.

=ÍNDICE(C2:C8,COINCIDENCIA(K2"-"K3,ÍNDICE(A2:A8"-"B2:B8,0),0))

Como hemos visto anteriormente, INDEX puede devolver múltiples valores al mismo tiempo. En este ejemplo, se combina cada combinación de celdas. Se ha utilizado “,0” como antes para indicarle a INDEX que devuelva todos los valores. INDEX convertirá la sección de matriz de la fórmula en múltiples valores que pueden ser procesados ​​por la función COINCIDIR.

La fórmula calcula el resultado correcto sin Ctrl + Shift + Enter.

Técnicamente, la función ÍNDICE se puede agregar a la combinación BUSCARV/ELEGIR para evitar la fórmula matricial. Pero eso es agregar una tercera función, que simplemente agrega más complicación.

INDEX MATCH sigue la lógica de las Tablas de Excel

Las tablas de Excel utilizan referencias estructuradas, donde se puede hacer referencia a una tabla de datos mediante los encabezados de columna. Si bien es posible utilizar BUSCARV para recuperar un valor de una columna específica, elimina los beneficios obtenidos al utilizar una tabla.

Normalmente, un número de columna dentro de BUSCARV es solo un número, lo que recupera el valor de la columna correcta. INDEX MATCH puede utilizar referencias estructuradas en todos los rangos, lo que hace que sea mucho más fácil entender el resultado calculado.

Lo único que BUSCARV ELEGIR hace mejor

Existen ciertas reglas de manejo de datos que, si se siguen, hacen que una hoja de cálculo sea fácil de administrar. Pero adivina qué, la mayoría de los usuarios de Excel no tienen idea de esto, por lo que podemos encontrar datos estructurados de algunas maneras "interesantes". La forma de referencia de INDEX puede abordar parte de esto realizando una búsqueda en varias tablas.

Mire la captura de pantalla a continuación. ¿Cómo se puede escribir una fórmula sencilla que busque los datos del mes correcto?

La fórmula en la celda H4 es:

=ÍNDICE((C2:C8,D2:D8,E2:E8,C12:C18,D12:D18,E12:E18),COINCIDIR(H2,A2:A8,0),,5)

Hay varios rangos (todos contenidos entre paréntesis) del primer argumento. Cada rango contiene las referencias de celda de cada mes. El último argumento, que es núm_área, determina de qué rango recuperar el resultado. En el ejemplo anterior se utiliza 5, por lo que se utilizará la quinta área D12:D18 para devolver el resultado. Cambie area_num a 2 y se utilizará el segundo rango (celdas D2 – D8).

Nota: Esta característica solo funciona cuando todos los rangos dentro del primer argumento de la función ÍNDICE están contenidos en una sola hoja.

La fórmula BUSCARV ELEGIR en la celda H6 es:

=BUSCARV(H2,ELEGIR({1,2,3,4,5,6,7},A2:A8,C2:C8,D2:D8,E2:E8,C12:C18,D12:D18,E12:E18 ),6,0)

Para buscar en la quinta área usando BUSCARV ELEGIR es necesario hacer referencia a la sexta área, ya que la columna de búsqueda es la primera referencia.

En este escenario, las fórmulas producen el mismo resultado. Sin embargo, BUSCARV ELEGIR puede hacer referencia a tablas en diferentes hojas, lo que en algunas circunstancias la convertirá en la mejor opción, pero con suerte, eso es muy raro.

Otros usos avanzados de INDEX/MATCH

Otro gran uso de INDEX/MATCH es usarlo como fórmula de búsqueda de imágenes .

Conclusión

Después de todo lo que te he mostrado, espero que estés de acuerdo en que INDEX MATCH no es sólo un reemplazo de BUSCARV.

BUSCARV por sí solo no es comparable a INDEX MATCH. Cuando se combina con CHOOSE, se acerca al nivel de funcionalidad pero es más lento y complejo de usar. INDEX MATCH es una increíble combinación de fórmulas que puede realizar la magia de Excel.

¿Sigo usando BUSCARV? Por supuesto que sí. Para tablas de datos simples sigue siendo una gran solución. A medida que aumenta la complejidad, INDEX MATCH se convierte en la mejor opción.

---