Por qué ÍNDICE y COINCIDIR son mejores que BUSCARV en Excel

Anteriormente les hemos explicado a los principiantes cómo usar las funciones básicas de BUSCARV (en inglés BUSCARV, la abreviatura significa "función de búsqueda vertical"). Y a los usuarios experimentados se les mostraron varias fórmulas más complicadas.

Y en este artículo intentaremos dar información sobre otro método para trabajar con la búsqueda vertical.

Quizás te estés preguntando: “¿Por qué es esto necesario?”. Y esto es necesario para mostrar todos los métodos de búsqueda posibles. Además, numerosas restricciones de BUSCARV a menudo impiden obtener el resultado deseado. En este sentido, INDEX( ) MATCH( ) es mucho más funcional y diverso, y también tiene menos restricciones.

COINCIDIR CON EL ÍNDICE

Dado que el propósito de esta guía es mostrar lo buena que es esta característica, Veamos la información básica sobre los principios de su funcionamiento. Y mostraremos ejemplos, y también consideraremos por qué, es mejor que BUSCARV ().

Sintaxis y uso de la función ÍNDICE

Esta función ayuda a encontrar el valor deseado entre las áreas de búsqueda especificadas según el número de columna o línea. Sintaxis:

=ÍNDICE(matriz, número de fila, número de columna):

• matriz: el área en la que se realizará la búsqueda;
• número de línea: el número de la línea que se buscará en la matriz especificada. Si se desconoce el número de fila, se debe especificar el número de columna;
• número de columna: el número de la columna que se encuentra en la matriz especificada. Si se desconoce el valor, se requiere un número de línea.

Un ejemplo de una fórmula simple:

=ÍNDICE(A1:S10,2,3)

La función buscará en el rango de A1 a C10. Los números muestran de qué fila (2) y columna (3) se muestra el valor deseado. El resultado será la celda C2.

Bastante simple, ¿verdad? Pero cuando trabaja con documentos reales, es poco probable que tenga información sobre números de columna o celdas. Para eso está la función MATCH().

Sintaxis y uso de la función MATCH

La función MATCH() busca el valor deseado y muestra su número aproximado en el área de búsqueda especificada.

La sintaxis de searchpos() se ve así:

=COINCIDIR(valor a buscar, matriz a buscar, tipo de coincidencia)

• valor de búsqueda: el número o texto que se va a encontrar;
• matriz buscada: el área donde se realizará la búsqueda;
• tipo de coincidencia: especifica si buscar el valor exacto o los valores más cercanos a él:
  • 1 (o ningún valor especificado): devuelve el valor más grande que es igual o menor que el valor que se especificó;
  • 0: muestra una coincidencia exacta con el valor buscado. En la combinación INDEX() MATCH() casi siempre necesitará una coincidencia exacta, por lo que escribimos 0;
  • -1: muestra el valor más pequeño que es mayor o igual que el valor especificado en la fórmula. La clasificación se realiza en orden descendente.

Por ejemplo, en el rango B1:B3 están registrados Nueva York, París, Londres. La siguiente fórmula mostrará el número 3 porque Londres ocupa el tercer lugar en la lista:

=EXPONER(Londres,B1:B3,0)

Cómo trabajar con la función ÍNDICE COINCIDIR 

Probablemente ya hayas comenzado a comprender el principio por el cual se construye el trabajo conjunto de estas funciones. En resumen, entonces INDEX() busca el valor deseado entre las filas y columnas especificadas. Y MATCH() muestra los números de estos valores:

=ÍNDICE(columna desde la que se devuelve el valor, COINCIDIR(valor a buscar, columna a buscar, 0))

¿Aún te cuesta entender cómo funciona? Quizás un ejemplo lo explique mejor. Suponga que tiene una lista de capitales del mundo y su población:

Para averiguar el tamaño de la población de una determinada capital, por ejemplo, la capital de Japón, utilizamos la siguiente fórmula:

=ÍNDICE(C2:C10, PARTIDO(Japón, A2:A10,0))

Explicación:

• La función MATCH() busca el valor "Japón" en la matriz A2:A10 y devuelve el número 3, porque Japón es el tercer valor de la lista. 
• Esta cifra va anúmero de línea” en la fórmula INDEX() y le dice a la función que imprima un valor de esta fila.

Entonces la fórmula anterior se convierte en la fórmula estándar. ÍNDICE(C2:C10,3). La fórmula busca desde las celdas C2 a C10 y devuelve datos de la tercera celda de este rango, es decir, C4, porque la cuenta regresiva comienza desde la segunda fila.

¿No quiere prescribir el nombre de la ciudad en la fórmula? Luego escríbalo en cualquier celda, diga F1, y utilícelo como referencia en la fórmula MATCH(). Y terminas con una fórmula de búsqueda dinámica:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

¡Importante! Número de líneas en matriz INDEX() debe ser igual al número de filas en matriz considerada en MATCH(), de lo contrario obtendrá un resultado incorrecto.

Espera un momento, ¿por qué no usas la fórmula BUSCARV()?

=BUSCARV(F1, A2:C10, 3, Falso)

 ¿Cuál es el punto de perder el tiempo tratando de resolver todas estas complejidades de INDEX MATCH?

En este caso, no importa qué función usar. Este es solo un ejemplo para comprender cómo funcionan juntas las funciones INDEX() y MATCH(). Otros ejemplos mostrarán de qué son capaces estas funciones en situaciones en las que VLOOKUP no tiene poder. 

ÍNDICE COINCIDIR o BUSCARV

Al decidir qué fórmula de búsqueda usar, muchos están de acuerdo en que INDEX() y MATCH() son muy superiores a BUSCARV. Sin embargo, muchas personas todavía usan BUSCARV(). En primer lugar, VLOOKUP() es más simple y, en segundo lugar, los usuarios no comprenden completamente todas las ventajas de trabajar con INDEX() y MATCH(). Sin este conocimiento, nadie aceptará dedicar su tiempo a estudiar un sistema complejo.

Estas son las ventajas clave de INDEX() y MATCH() sobre VLOOKUP():

• Buscar de derecha a izquierda. VLOOKUP() no puede buscar de derecha a izquierda, por lo que los valores que está buscando siempre deben estar en las columnas más a la izquierda de la tabla. Pero INDEX() y MATCH() pueden manejar esto sin problema. Este artículo le dirá cómo se ve en la práctica: cómo encontrar el valor deseado en el lado izquierdo.

1. Adición o eliminación segura de columnas. La fórmula BUSCARV() muestra resultados incorrectos al eliminar o agregar columnas porque BUSCARV() necesita el número de columna exacto para tener éxito. Naturalmente, cuando se agregan o eliminan columnas, sus números también cambian. 

Y en las fórmulas INDEX() y MATCH(), se especifica un rango de columnas, no columnas individuales. Como resultado, puede agregar y eliminar columnas de manera segura sin tener que actualizar la fórmula cada vez.

1. Sin límites en los volúmenes de búsqueda. Al usar BUSCARV(), el número total de criterios de búsqueda no debe exceder los 255 caracteres o obtendrá un #¡VALOR! Entonces, si sus datos contienen una gran cantidad de caracteres, INDEX() y MATCH() son la mejor opción.
2. Alta velocidad de procesamiento. Si sus tablas son relativamente pequeñas, es poco probable que note alguna diferencia. Pero, si la tabla contiene cientos o miles de filas y, en consecuencia, hay cientos y miles de fórmulas, INDEX () y MATCH () se adaptarán mucho más rápido que BUSCARV (). El hecho es que Excel procesará solo las columnas especificadas en la fórmula, en lugar de procesar toda la tabla. 

El impacto en el rendimiento de BUSCARV() será especialmente notable si su hoja de trabajo contiene una gran cantidad de fórmulas como BUSCARV() y SUMA(). Se requieren verificaciones separadas de las funciones BUSCARV () para analizar cada valor en una matriz. Entonces, Excel tiene que procesar una gran cantidad de información, y esto ralentiza el trabajo de manera significativa.

Ejemplos de fórmulas 

Ya hemos descubierto la utilidad de estas funciones, por lo que podemos pasar a la parte más interesante: la aplicación del conocimiento en la práctica.

Formula para buscar de derecha a izquierda

Como ya se mencionó, BUSCARV no puede realizar esta forma de búsqueda. Entonces, si los valores deseados no están en la columna más a la izquierda, BUSCARV() no producirá ningún resultado. Las funciones INDEX() y MATCH() son más versátiles, y la ubicación de los valores no juega un papel importante para que funcionen.

Por ejemplo, agregaremos una columna de rango al lado izquierdo de nuestra tabla e intentaremos averiguar qué rango en términos de población ocupa la capital de Nuestro País.

En la celda G1, escribimos el valor a encontrar y luego usamos la siguiente fórmula para buscar en el rango C1:C10 y devolver el valor correspondiente de A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Rápido. Si planea usar esta fórmula para varias celdas, asegúrese de corregir los rangos usando direccionamiento absoluto (por ejemplo, $A$2: $A$10 y $C$2: 4C$10).

ÍNDICE MÁS EXPUESTOS MÁS EXPUESTOS  para buscar en columnas y filas

En los ejemplos anteriores, hemos utilizado estas funciones como reemplazo de BUSCARV() para devolver valores de un rango predefinido de filas. Pero, ¿qué pasa si necesita hacer una búsqueda matricial o bilateral?

Suena complicado, pero la fórmula para tales cálculos es similar a la fórmula estándar INDEX() MATCH(), con solo una diferencia: la fórmula MATCH() debe usarse dos veces. La primera vez para obtener el número de fila y la segunda vez para obtener el número de columna:

=ÍNDICE(matriz, COINCIDIR(valor de búsqueda vertical, columna de búsqueda, 0), COINCIDIR(valor de búsqueda horizontal, fila de búsqueda, 0))

Miremos la siguiente tabla e intentemos hacer una fórmula. ÍNDICE() EXPRESS() EXPRESS() para mostrar datos demográficos en un país específico para un año seleccionado.

El país objetivo está en la celda G1 (búsqueda vertical) y el año objetivo está en la celda G2 (búsqueda horizontal). La fórmula se verá así:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Cómo funciona esta fórmula

Al igual que con cualquier otra fórmula compleja, son más fáciles de entender si se dividen en ecuaciones individuales. Y luego puedes entender lo que hace cada función individual:

• COINCIDIR (G1,A2:A11,0) – busca un valor (G1) en el rango A2:A11 y muestra el número de este valor, en nuestro caso es 2;
• BUSCAR(G2,B1:D1,0) – busca un valor (G2) en el rango B1:D1. En este caso, el resultado fue 3.

Los números de fila y columna encontrados se envían al valor correspondiente en la fórmula INDEX():

=ÍNDICE(B2:D11,2,3)

Como resultado, tenemos un valor que está en una celda en la intersección de 2 filas y 3 columnas en el rango B2:D11. Y la fórmula muestra el valor deseado, que está en la celda D3.

Busque por múltiples condiciones con INDEX y MATCH

Si ha leído nuestra guía de BUSCARV(), probablemente haya probado varias fórmulas de búsqueda. Pero este método de búsqueda tiene una limitación significativa: la necesidad de agregar una columna auxiliar.

Pero la buena noticia es que Con INDEX() y MATCH() puede buscar múltiples condiciones sin tener que editar o cambiar su hoja de trabajo.

Aquí está la fórmula general de búsqueda de múltiples condiciones para INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

La nota: esta fórmula debe usarse junto con el atajo de teclado CTRL+MAYÚS+ENTRAR.

Suponga que necesita encontrar el valor que está buscando en función de 2 condiciones: Comprador и Producto.

Esto requiere la siguiente fórmula:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

En esta fórmula, C2:C10 es el rango en el que se realizará la búsqueda, F1 - esta condición, A2: A10 — es el rango para comparar la condición, F2 – condición 2, V2: V10 – rango para la comparación de la condición 2.

No olvide presionar la combinación al final del trabajo con la fórmula CTRL + MAYÚS + ENTRAR – Excel cerrará automáticamente la fórmula con llaves, como se muestra en el ejemplo:

Si no desea utilizar una fórmula de matriz para su trabajo, agregue otro ÍNDICE() a la fórmula y presione ENTRAR, se verá como en el ejemplo:

Cómo funcionan estas fórmulas

Esta fórmula funciona de la misma manera que la fórmula estándar INDEX() MATCH(). Para buscar varias condiciones, simplemente cree varias condiciones falsas y verdaderas que representen las condiciones individuales correctas e incorrectas. Y luego estas condiciones se aplican a todos los elementos correspondientes de la matriz. La fórmula convierte los argumentos Falso y Verdadero en 0 y 1, respectivamente, y genera una matriz donde 1 son los valores coincidentes que se encontraron en la cadena. MATCH() encontrará el primer valor que coincida con 1 y lo pasará a la fórmula INDEX(). Y, a su vez, devolverá el valor ya deseado en la línea especificada de la columna deseada.

Una fórmula sin una matriz depende de la capacidad de INDEX() para manejarlos por sí solo. El segundo ÍNDICE() en la fórmula coincide con falso (0), por lo que pasa la matriz completa con esos valores a la fórmula MATCH(). 

Esta es una explicación bastante larga de la lógica detrás de esta fórmula. Para más información lea el artículo “ÍNDICE COINCIDIR con múltiples condiciones".

PROMEDIO, MAX y MIN en ÍNDICE y PARTIDO

Excel tiene sus propias funciones especiales para encontrar promedios, máximos y mínimos. Pero, ¿qué sucede si desea obtener datos de la celda asociada con esos valores? En este caso PROMEDIO, MÁX. y MÍN. deben usarse junto con ÍNDICE y COINCIDIR.

ÍNDICE COINCIDIR y MÁX.

Para encontrar el valor más grande en la columna D y mostrarlo en la columna C, use la fórmula: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

ÍNDICE DE COINCIDENCIA y MÍN.

Para encontrar el valor más pequeño en la columna D y mostrarlo en la columna C, use la siguiente fórmula:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

ÍNDICE DE BÚSQUEDA y SERPIENTE

Para encontrar el valor promedio en la columna D y mostrar este valor en C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Dependiendo de cómo se escriban sus datos, el tercer argumento de MATCH() es 1, 0 o -1:

• si las columnas están ordenadas en orden ascendente, establezca 1 (entonces la fórmula calculará el valor máximo, que es menor o igual que el valor promedio);
• si el orden es descendente, entonces -1 (la fórmula generará el valor mínimo que es mayor o igual que el promedio);
• si la matriz de búsqueda contiene un valor que es exactamente igual al promedio, configúrelo en 0. 

 En nuestro ejemplo, la población está ordenada en orden descendente, por lo que ponemos -1. Y el resultado es Tokio, ya que el valor de población (13,189) es el más cercano al valor medio (000).

BUSCARV() también puede realizar tales cálculos, pero solo como una fórmula de matriz: BUSCARV con PROMEDIO, MIN y MAX.

ÍNDICE COINCIDIR y ESND/IFERROR

Probablemente ya haya notado que si la fórmula no puede encontrar el valor deseado, arroja un error #N / A. Puede reemplazar el mensaje de error estándar con algo más informativo. Por ejemplo, establezca el argumento en la fórmula En el siglo 13,:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Con esta fórmula, si ingresa datos que no están en la tabla, el formulario le dará el mensaje especificado.

Si desea capturar todos los errores, excepto por En el siglo 13, utilizar IFERROR:

=SI.ERROR(ÍNDICE(C2:C10,COINCIDIR(F1,A2:A10,0)), "¡Algo salió mal!")

Pero recuerde que enmascarar errores de esta manera no es una buena idea, porque los errores estándar informan violaciones en la fórmula.

Esperamos que haya encontrado útil nuestra guía para usar la función INDEX MATCH().