Funciones de búsqueda, INDICE y COINCIDIR (INDEX y MATCH)

Excel posee varias funciones para buscar información en un rango de celdas o matriz (array). La mas común y utilizada de ellas es BUSCARV. Estas funciones permiten buscar un valor determinado en un rango de celdas y devolver un valor ubicado en la misma fila pero en una columna ubicada a la derecha del valor encontrado.
En algunas ocasiones tenemos que devolver un valor que se encuentra a la izquierda del valor buscado. Una solución a este problema es modificar el formato de la lista o rango de celdas pero a veces esto no es posible. En ese caso podemos usar una combinación de formulas INDICE y COINCIDIR (INDEX y MATCH para la versión en inglés) para lograr el resultado.


Veamos una descripción de lo que realizan estas dos formulas


COINCIDIR devuelva la posición (con respecto al primer elemento) del elemento buscado dentro de un rango de celdas. La función toma tres parámetros, el primero es el valor buscado, el segundo es el rango donde se desea buscar el elemento. Por último podemos indicar si queremos una equivalencia exacta el valor próximo mayor o menor. Si el valor buscado no se encuentra, la función devuelve el error #N/A.


INDICE devuelve el elemento que se encuentra en una posición determinada dentro de un rango de celdas o matriz. La función toma tres parámetros, el primero es el rango de celdas a buscar o matriz, el segundo el número de fila donde se encuentra el elemento. El tercero indica el número de la columna, este parámetro puede ser omitido y en ese caso se toma la primer columna.
Las posiciones son relativas al comienzo del rango donde se realiza la búsqueda. Por ejemplo si buscamos en el rango B15:B25 y deseamos el elemento que se encuentra en B20 debemos ingresar el número 5 como parámetro.


Veamos algunos ejemplos


Utilizando el mismo ejemplo de una tabla de posiciones que presenté en el artículo de las funciones K.ESIMO.MAYOR y K.ESIMO.MENOR podemos utilizar las funciones INDICE y COINCIDIR para saber cual es el nombre del equipo que tiene más puntos.

Para traer el nombre del equipo que se encuentra primero utilizamos esta fórmula =INDICE($A$4:$A$10;COINCIDIR(F4;$B$4:$B$10;0)).

Si desea obtener una copia del ejemplo haga clic aqui.

Realizar sumas condicionales con mas de un criterio SUMAR.SI.CONJUNTO

La función SUMAR.SI permite realizar una suma en un rango determinado si se cumple cierto criterio definido en uno de sus parámetros siendo una función es muy utilizada. Uno de los principales problemas de esta función es que solo se puede poner un criterio.

Para resolver este problema antes de la introducción de esta nueva función se podía utilizar en conjunto las funciones SUMAR y SI e introducirlas como un matriz (Array). En un artículo futuro explicaré los detalles de las funciones de matrices.


Excel 2007 introduce una nueva función denominada SUMAR.SI.CONJUNTO que permite realizar una suma utilizando múltiples criterios, hasta 127 criterios pueden ser incluidos.

Esta función toma como primer parámetro el rango a sumar y luego se tienen que indicar el criterio para sumar. Este se define de a pares, es decir para cada criterio tenemos dos parámetros, el primero es el rango dónde queremos aplicar la condición y el segundo es las condición que queremos definir.


Veamos algunos ejemplos

Si tenemos una tabla donde se tienen las ventas por producto y país y queremos obtener totales para un país o un producto o una combinación podemos utilizar las siguientes formulas :

Para obtener el total de ventas para un país, usamos la función normal SUMAR.SI, =SUMAR.SI(A2:A14;"Argentina";C2:C14)

Para obtener el total de ventas para Manzanas, usamos la función =SUMAR.SI(B2:B14;"Manzana";C2:C14)

Para obtener el total de ventas de Manzanas en Argentina, podemos usar =SUMAR.SI.CONJUNTO(C2:C14;A2:A14;"Argentina";B2:B14;"Manzana")

Si no tienen Excel 2007 pueden lograr el mismo resultado utilizando una función ingresada como matriz, {=SUMA(SI((A2:A14="Argentina")*(B2:B14="Manzana");C2:C14))}. Para ingresar la función matricial se debe presionar Ctrl + Shift + Enter
Para obtener mas información de las funciones matriciales o arrays pueden leer el siguiente nota.

Cambiando los criterios se puede analizar cualquier lista de Excel y obtener rápidamente un resultado que de otra forma llevaría varios pasos.

Descubriendo las funciones de Excel 2, K.ESIMO.MAYOR y K.ESIMO.MENOR (LARGE y SMALL)

La función K.ESIMO.MAYOR devuelve el k-esimo valor más grande de una lista. Por su parte la función K.ESIMO.MENOR devuelve el k-esimo valor más pequeño de una lista. Para ambas funciones se toman dos parámetros como argumentos, el primero es el rango o lista de valores a evaluar y el segundo es el nro. de elemento más grande o más pequeño que se desea.

Si el nro. de elemento ingresado es mayor que la cantidad de elementos en el rango seleccionado, la fórmula devuelve el error#¡NUM!.

Existen otras dos funciones similares que son MAX y MIN, estas funciones pueden devolver el más grande y más pequeño de una lista respectivamente. Estas funciones son equivalentes a K.ESIMO.MAYOR y K.ESIMO.MENOR con el segundo parámetro en 1.


Veamos algunos Ejemplos

Si tenemos una tabla de resultados, que no esta ordenada y por lo tanto el primer equipo no es el que tiene mayor cantidad de puntos y el último no es el que tiene menor cantidad de puntos :

Con las formulas K.ESIMO.MAYOR y K.ESIMO.MENOR podemos encontrar los primeros tres y los últimos tres equipos.

Para el primer equipo =K.ESIMO.MAYOR(A4:A10,1), para el segundo =K.ESIMO.MAYOR(A4:A10,2) y así sucesivamente.

Para el último equipo =K.ESIMO.MENOR(A4:A10,1), para el anteúltimo =K.ESIMO.MENOR(A4:A10,1) y así sucesivamente.

Luego con la funciones INDICE y COINCIDIR (o INDEX y MATCH para la versión en inglés) se puede buscar el equipo correspondiente al puntaje.

Esta es una de las tantas aplicaciones que pueden tener estas formulas en artículos futuros las utilizaremos para realizar otras tareas más complejas.

Si tienen preguntas no duden en poner comentarios.

Descubriendo las funciones de Excel, SI.ERROR (IFERROR)

Excel posee muchas funciones (la versión 2007 tiene 359 funciones) y algunas de ellas muy utilizadas, pero otras son más desconocidas y en una serie de artículos intentaré explicarles brindando algunos ejemplos prácticos.

En la primer nota de esta serie utilizaremos la función SI.ERROR o IFERROR si poseen la versión en Inglés.

Esta función evaluará el resultado de una formula y si produce un error nos permitirá tomar una acción determinada. Esta función evalúa los siguientes tipos de errores : #N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!
Se toman dos parámetros, el primero es la función que se desea evaluar y el segundo es la acción que se desea hacer si la formula evaluada generó un error.


Veamos algunos ejemplos

Si se intenta dividir por cero Excel mostrará el error #¡DIV/0! para evitar este mensaje o realizar otra acción se puede utilizar :

=SI.ERROR(E6/F6;”Error”) o bien dejar el resultado de la celda en blanco =SI.ERROR(E6/F6;””) como se ve en el ejemplo y tenemos como ventaja la posibilidad de realizar una suma.

Otro ejemplo para esta función, es para evitar el valor #N/A que puede ser devuelto por la función BUSCARV (VLOOKUP para la versión en inglés) :

=BUSCARV(“Total”; F1:G20; 2; Falso). Si el valor “Total” no se encuentra en el rango buscado Excel devolverá el error #N/A, para no mostrar dicho error podemos utilizar la siguiente función : =SI.ERROR(BUSCARV(“Total”; F1:G20; 2; Falso),””)

Antes de la introducción esta fórmula era común utilizar la función ESNOD para evaluar el resultado del BUSCARV, pero la desventaja aquí es que la función BUSCARV se realizaba dos veces si el valor era encontrado.

Por ejemplo =SI(ESNOD(BUSCARV(“Total”; F1:G20; 2; Falso));””; BUSCARV(“Total”; F1:G20; 2; Falso))
Con lo cual esta nueva formula resuelve mas rápido las búsquedas y también es mas fácil ingresarla ya que no se debe registrar una formula tan larga.

Como verán la función puede aplicarse en diversas situaciones permitiendo solucionar muchos inconvenientes.

En próximos artículos revisaremos otras aplicaciones de esta fórmula como así también otras formulas incluidas en Excel pero no tan conocidas.