Función ÍNDICE y COINCIDIR en Excel

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 ÍNDICE-COINCIDIR 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 una búsqueda horizontal, una búsqueda vertical, una búsqueda bidireccional, una búsqueda a la izquierda, una búsqueda que distingue 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 al mismo tiempo que una herramienta flexible que recupera el valor en una ubicación dada en un rango. En otras palabras, devuelve el contenido de una celda, especificado por desplazamiento de fila y columna.

Sintaxis:

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

Parámetros:

• referencia: la array de celdas en las que se compensará. Puede ser un solo rango o un conjunto de datos completo en una tabla de datos.
• fila [opcional]: el número de filas compensadas. Significa que si elegimos un rango de referencia de tabla como «A1: A5», entonces la celda/contenido que queremos extraer está a la distancia vertical. Aquí, para la fila A1 será 1, para la fila A2 = 2, y así sucesivamente. Si le damos fila = 4, entonces extraerá A4. Como la fila es opcional, si no especificamos ningún número de fila, extrae filas enteras en el rango de referencia. Eso es A1 a A5 en este caso.
• columna [opcional]: el número de columnas de compensación. Significa que si elegimos un rango de referencia de tabla como «A1: B5», entonces la celda/contenido que queremos extraer está a la 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 columna = 1, para B2 fila = 2 columna = 2 y así sucesivamente. Si le damos fila = 5 y columna 2, extraerá B5. Como la columna es opcional, si no especificamos ninguna fila, no. luego extraerá toda la columna en el rango de referencia. Por ejemplo, si damos fila = 2 y columna como vacías, extraerá (A2:B2). Si no especificamos Fila y columna, extraerá la tabla de referencia completa que es (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: Solo se mencionan las filas.

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

Caso 3: se mencionan tanto las filas como las columnas.

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

Caso 4: Solo se mencionan Columnas.

Comando de entrada: =INDICE(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í viene el concepto de función de coincidencia, que identificará filas y columnas en función de alguna condición.

Función PARTIDO

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. MATCH 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 array unidimensional que se buscará. Puede ser una sola fila o una sola columna. Por ejemplo,->A1:A10, A2:D2, etc.
• search_type [opcional]: El método de búsqueda. = 1 (predeterminado) encuentra el valor más grande menor o igual que search_key cuando el rango se ordena 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 que search_key cuando el rango se ordena en orden descendente.

El número de fila o el número de columna se pueden encontrar usando la función de coincidencia y se pueden usar dentro de la función de índice, por lo que si hay algún detalle sobre un elemento, entonces se puede extraer toda la información sobre el elemento buscando la fila/columna del elemento usando 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 MATCH. 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: =COINCIDIR(“Sur de la India”,C3:C10)                           

  
Caso 3: Tipo de búsqueda -1.

Comando de entrada: =COINCIDIR(“Sur de la India”,C3:C10,-1)                             

ÍNDICE-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 filas y columnas, entonces la posición de filas y columnas se puede proporcionar mediante 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 la columna «Comida» y 0 significa Coincidencia exacta. Devolverá el número de fila de Masala Dosa.

Paso 2: Encuentra el costo de Masala Dosa. Use la función ÍNDICE para encontrar el costo de Masala Dosa. Al sustituir la consulta de función MATCH anterior dentro de la función INDEX 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 bidireccional con INDEX-MATCH juntos

En el ejemplo anterior, la posición de la columna del atributo «Coste» 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 la fila, así como el valor de la columna, se proporcionan a través de 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 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, el costo de Rs 140/- es conocido, existe la necesidad de extraer el «Alimento». 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 usando BUSCARV no es posible obtener el nombre del alimento. 

Para superar esta desventaja, se puede utilizar la función ÍNDICE-COINCIDIR con la búsqueda izquierda. 
Paso 1: Primero extraiga la posición de la fila de Cost 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 sensible a mayúsculas y minúsculas

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

1. “Dhokla”
2. «dhokla»
3. “DhOkLA”

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

Función Exacta: La función EXACTA de Excel compara dos strings de texto, teniendo en cuenta los caracteres en mayúsculas y minúsculas, y devuelve VERDADERO si son iguales y FALSO en caso contrario. EXACTO distingue entre mayúsculas y minúsculas.

Ejemplos:

1. EXACTO(“DHOKLA”,”DHOKLA”): Esto devolverá Verdadero.
2. EXACTO(“DHOKLA”,”Dhokla”): Esto devolverá Falso.
3. EXACTO(“DHOKLA”,”dhokla”): Esto devolverá Falso.
4. EXACTO(“DHOKLA”,”DhOkLA”): Esto devolverá Falso.

Ejemplo: digamos que la tarea es buscar el tipo de alimento «Dhokla», pero distingue 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 en 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 una búsqueda basada en múltiples criterios. En otras palabras, una búsqueda que coincide con 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 Pasta donde 

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

Entonces, en este ejemplo, hay tres criterios para realizar un Match. A continuación se muestran los pasos para la búsqueda basada en múltiples criterios:

Paso 1: Primero haga coincidir 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 valores booleanos. Eso es cierto donde 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 costos) como valores booleanos. Eso es Verdadero donde Costo=60 si no 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.

Paso 4: Multiplique el resultado del primer, segundo y tercer criterio. Esta será la intersección de todas las condiciones y convertirá Boolean True / False 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, 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.