INDEX-MATCH se ha convertido en una herramienta más popular para Excel ya que resuelve la limitación de la función BUSCARV y es más fácil de usar. La función INDEX-MATCH en Excel tiene una serie de ventajas sobre la función BUSCARV:

1. INDEX y MATCH son más flexibles y rápidos que Vlookup
2. Es posible ejecutar búsquedas horizontales, búsquedas 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.
3. En datos ordenados, INDEX-MATCH es un 30% más rápido que BUSCARV. Esto significa que en un conjunto de datos más grande, un 30% más rápido tiene más sentido.

Comencemos con los conceptos detallados de cada ÍNDICE y PARTIDO.

Función ÍNDICE

La función ÍNDICE en Excel es muy poderosa y al mismo tiempo es una herramienta flexible que recupera el valor en una ubicación determinada dentro de un rango. En otras palabras, devuelve el contenido de una celda, especificada por el desplazamiento de fila y columna.

Sintaxis:

=INDEX(reference, [row], [column])>

Parámetros:

referencia: la matriz de celdas en las que se desplazará. Puede ser un rango único o un conjunto de datos completo en una tabla de datos. fila [opcional]: el número de filas desplazadas. Significa que si elegimos un rango de referencia de tabla como A1:A5, entonces la celda/contenido que queremos extraer está a tanta distancia vertical. Aquí, para A1 la fila será 1, para A2 la fila = 2, y así sucesivamente. Si damos fila = 4, extraerá A4. Como la fila es opcional, si no especificamos ningún número de fila, se extraen filas enteras en el rango de referencia. En este caso, es de A1 a A5. columna [opcional]: el número de columnas de desplazamiento. Significa que si elegimos un rango de referencia de tabla como A1:B5, entonces la celda/contenido que queremos extraer está a tanta distancia horizontal. Aquí, para A1 la fila será 1 y la columna será 1, para B1 la fila será 1 pero la columna será 2 de manera similar para A2 fila = 2 columnas = 1, para B2 fila = 2 columnas = 2 y así sucesivamente. Si damos la fila = 5 y la columna 2, extraerá B5. Como la columna es opcional, si no especificamos ninguna fila, no. luego extraerá la columna completa en el rango de referencia. Por ejemplo, si damos la fila = 2 y la columna están vacías, se extraerá (A2:B2). Si no especificamos la fila y la columna, extraerá la tabla de referencia completa, es decir (A1: B5).

Tabla de referencia: La siguiente tabla se utilizará como tabla de referencia para todos los ejemplos de la función ÍNDICE. La primera celda está en B3 (COMIDA) y la última celda diagonal está en F10 (180).

Ejemplos: A continuación se muestran algunos ejemplos de funciones de índice.

Caso 1: No se mencionan filas ni columnas.

Comando de entrada: =ÍNDICE(B3:C10)

Caso 2: Sólo se mencionan filas.

Comando de entrada: =ÍNDICE(B3:C10,2)

Caso 3: Se mencionan tanto filas como columnas.

Comando de entrada: =ÍNDICE(B3:D10,4,2)

Caso 4: Sólo se mencionan las columnas.

Comando de entrada: =ÍNDICE(B3 : D10 , , 2)

Problema con la función ÍNDICE: El problema con la función ÍNDICE es que es necesario especificar filas y columnas para los datos que estamos buscando. Supongamos que estamos tratando con un conjunto de datos de aprendizaje automático de 10000 filas y columnas, entonces será muy difícil buscar y extraer los datos que estamos buscando. Aquí surge el concepto de función de coincidencia, que identificará filas y columnas en función de alguna condición.

Función COINCIDIR

Recupera la posición de un elemento/valor en un rango. Es una versión menos refinada de BUSCARV o BUSCARH que solo devuelve la información de ubicación y no los datos reales. COINCIDIR no distingue entre mayúsculas y minúsculas y no le importa si el rango es Horizontal o Vertical.

Sintaxis:

=MATCH(search_key, range, [search_type])>

Parámetros:

search_key: el valor a buscar. Por ejemplo, 42, Gatos o I24. rango: la matriz unidimensional que se buscará. Puede ser una sola fila o una sola columna. Por ejemplo,->A1:A10, A2:D2, etc. tipo_búsqueda [opcional]: el método de búsqueda. = 1 (predeterminado) encuentra el valor más grande menor o igual a search_key cuando el rango está ordenado en orden ascendente.
• = 0 encuentra el valor exacto cuando el rango no está ordenado.
• = -1 encuentra el valor más pequeño mayor o igual a search_key cuando el rango está ordenado en orden descendente.

El número de fila o el número de columna se puede encontrar usando la función de coincidencia y puede usarlo dentro de la función de índice, de modo que si hay algún detalle sobre un elemento, toda la información se puede extraer sobre el elemento buscando la fila/columna del elemento usando la coincidencia. luego anidándolo en la función de índice.

Tabla de referencia: La siguiente tabla se utilizará como tabla de referencia para todos los ejemplos de la función COINCIDIR. La primera celda está en B3 (COMIDA) y la última celda diagonal está en F10 (180)

Ejemplos: A continuación se muestran algunos ejemplos de la función MATCH:

Caso 1: Tipo de búsqueda 0, significa coincidencia exacta.

Comando de entrada: = COINCIDIR (sur de la India, C3: C10,0)

Caso 2: Tipo de búsqueda 1 (predeterminado).

Comando de entrada: = PARTIDO (sur de la India, C3: C10)

Ups concepto en java

Caso 3: Tipo de búsqueda -1.

Comando de entrada: = PARTIDO (sur de la India, C3: C10, -1)

INDEX-MATCH Juntos

En los ejemplos anteriores, los valores estáticos de filas y columnas se proporcionaron en la función ÍNDICE. Supongamos que no hay conocimiento previo sobre la posición de las filas y columnas, entonces la posición de las filas y columnas se puede proporcionar usando la función COINCIDIR. Esta es una forma dinámica de buscar y extraer valor.

Sintaxis:

 =INDEX(Reference Table , [Match(SearchKey,Range,Type)/StaticRowPosition],  [Match(SearchKey,Range,Type)/StaticColumnPosition])>

Tabla de referencia: Se utilizará la siguiente tabla de referencia. La primera celda está en B3 (COMIDA) y la última celda diagonal está en F10 (180)

Ejemplo: Digamos que la tarea es encontrar el costo de Masala Dosa. Se sabe que la columna 3 representa el costo de los artículos, pero se desconoce la posición de la fila de Masala Dosa. El problema se puede dividir en dos pasos:

Paso 1: Encuentra la posición de Masala Dosa usando la fórmula:

 =MATCH('Masala Dosa',B3:B10,0)>

Aquí B3:B10 representa el alimento de la columna y 0 significa coincidencia exacta. Devolverá el número de fila de Masala Dosa.

Paso 2: Encuentre el costo de Masala Dosa. Utilice la función ÍNDICE para encontrar el costo de Masala Dosa. Sustituyendo la consulta de la función COINCIDIR anterior dentro de la función ÍNDICE en el lugar donde se requiere la posición exacta de Masala Dosa, y el número de columna de costo es 3, que ya se conoce.

=INDEX(B3:F10, MATCH('Masala Dosa', B3:B10 , 0) ,3)>

Búsqueda de dos formas con INDEX-MATCH juntas

En el ejemplo anterior, la posición de la columna del atributo Costo estaba codificada. Entonces, no fue completamente dinámico.

Caso 1: Supongamos que tampoco se conoce el número de columna de Costo, entonces se puede obtener usando la fórmula:

 =MATCH('Cost',B3:F3,0)>

Aquí B3: F3 representa la columna de encabezado.

Caso 2: Cuando el valor de la fila y de la columna se proporciona mediante la función COINCIDIR (sin proporcionar un valor estático), se denomina búsqueda bidireccional. Se puede lograr usando la fórmula:

 =INDEX(B3:F10, MATCH('Masala Dosa',B3:B10, 0) , MATCH('Cost' ,B3:F3 ,0))>

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 por la izquierda. Significa que es posible extraer la posición de la fila de un elemento usando cualquier atributo a la derecha y se puede extraer el valor de otro atributo a la izquierda.

Por ejemplo, digamos que compre alimentos cuyo costo debería ser de 140 Rs. Indirectamente estamos diciendo comprar Biryani. En este ejemplo, se conoce el costo de 140 rupias/-, es necesario extraer los alimentos. Dado que la columna Costo se coloca a la derecha de la columna Alimentos. Si se aplica BUSCARV, no podrá buscar en el lado izquierdo de la columna Costo. Es por eso que al usar BUSCARV no es posible obtener el nombre del alimento.

Para superar esta desventaja, se puede utilizar la función INDEX-MATCH de búsqueda izquierda.
Paso 1: Primero extraiga la posición de la fila del Costo 140 Rs usando la fórmula:

 =MATCH(140, D3:D10,0)>

Aquí D3: D10 representa la columna Costo donde se realiza la búsqueda del número de fila Costo 140 Rs.

Paso 2: Después de obtener el número de fila, el siguiente paso es usar la función ÍNDICE para extraer el nombre del alimento usando la fórmula:

 =INDEX(B3:B10, MATCH(140, D3:D10,0))>

Aquí B3: B10 representa la columna de alimentos y 140 es el costo del alimento.

Búsqueda que distingue entre mayúsculas y minúsculas

Por sí sola, la función COINCIDIR no distingue entre mayúsculas y minúsculas. Esto significa que si hay un nombre de alimento DHOKLA y se usa la función COINCIDIR con la siguiente palabra de búsqueda:

1. Dhokla
2. dhokla
3. DhOkLA

Todos devolverán la posición de fila de DHOKLA. Sin embargo, la función EXACTA se puede utilizar con ÍNDICE y COINCIDIR para realizar una búsqueda que respete las mayúsculas y las minúsculas.

Función exacta: La función EXACTA de Excel compara dos cadenas de texto, teniendo en cuenta los caracteres en mayúsculas y minúsculas, y devuelve VERDADERO si son iguales y FALSO si no. EXACT distingue entre mayúsculas y minúsculas.

Ejemplos:

EXACTO(DHOKLA,DHOKLA): Esto devolverá Verdadero. EXACTO (DHOKLA, Dhokla): Esto devolverá Falso. EXACTO(DHOKLA,dhokla): Esto devolverá Falso. EXACTO(DHOKLA,DhOkLA): Esto devolverá Falso.

Ejemplo: Digamos que la tarea es buscar el tipo de alimento Dhokla, pero distinguiendo entre mayúsculas y minúsculas. Esto se puede hacer usando la fórmula-

 =INDEX(C3:C10, MATCH(TRUE , EXACT('Dhokla', B3:B10) ,0))>

Aquí la función EXACTA devolverá Verdadero si el valor de la Columna B3:B10 coincide con Dhokla con el mismo caso; de lo contrario, devolverá Falso. Ahora la función COINCIDIR se aplicará en la columna B3: B10 y buscará una fila con el valor exacto VERDADERO. Después de eso, la función ÍNDICE recuperará el valor de la columna C3:C10 (columna de tipo de alimento) en la fila devuelta por la función COINCIDIR.

Búsqueda de criterios múltiples

Uno de los problemas más complicados en Excel es la búsqueda basada en múltiples criterios. En otras palabras, una búsqueda que coincide en más de una columna al mismo tiempo. En el siguiente ejemplo, la función ÍNDICE y COINCIDIR y la lógica booleana se utilizan para hacer coincidir 3 columnas:

1. Alimento.
2. Costo.
3. Cantidad.

Para extraer el costo total.

Ejemplo: Digamos que la tarea es calcular el costo total de la pasta donde

Comida: Pastas. Costo: 60. Cantidad: 1.

Entonces, en este ejemplo, hay tres criterios para realizar una coincidencia. A continuación se detallan los pasos para la búsqueda basada en múltiples criterios.

Paso 1: Primero combine la columna de alimentos (B3:B10) con pasta usando la fórmula:

 'PASTA' = B3:B10>

Esto convertirá los valores B3:B10 (columna de alimentos) como booleanos. Eso es cierto cuando la comida es pasta; de lo contrario, es falso.

Paso 2: Después de eso, haga coincidir los criterios de costo de la siguiente manera:

 60 = D3:D10>

Esto reemplazará los valores D3:D10 (columna de costo) como booleanos. Eso es Verdadero donde Costo = 60 o Falso.

Paso 3: El siguiente paso es hacer coincidir el tercer criterio que es Cantidad = 1 de la siguiente manera:

 1 = E3:E10>

Esto reemplazará la columna E3:E10 (columna de cantidad) como Verdadero donde Cantidad = 1; de lo contrario, será Falso.

Etapa 4: Multiplica el resultado del primer, segundo y tercer criterio. Esta será la intersección de todas las condiciones y convertirá el valor booleano Verdadero/Falso como 1/0.

Paso 5: Ahora el resultado será una columna con 0 y 1. Aquí use la función COINCIDIR para encontrar el número de fila de columnas que contienen 1. Porque si una columna tiene el valor 1, entonces significa que satisface los tres criterios.

Paso 6: Después de obtener el número de fila, use la función ÍNDICE para obtener el costo total de esa fila.

 =INDEX(F3:F10, MATCH(1, ('Pasta'=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 ))>

Aquí F3:F10 representa la columna de costo total.