La función INDICE, todo (o casi todo) lo que hay que saber

En la nota Traducción automática en hoja de Excel propuse dar un premio (el Planificador de Tareas JLD) a los primeros tres lectores que respondieron acertádamente a la pregunta qué tiene de particular la forma la forma en que se usa la función INDICE en el ejemplo de la nota.
Si bien hubo tres ganadores, ninguna respuesta fue totalmente exacta, lo que me impulsó a escribir esta nota.

La función INDICE es una de las más importantes y versátiles funciones del arsenal de Excel (y hay quien la corona como la más importante endilgándole superlatívamente el adjetivo "imponente").

La función INDICE permite encontrar el valor de un elemento dentro de una rango. Este rango puede ser un "vector" (un rango unidimensional de una fila como A2:M2 o de una columna como  A2:A10), o una matriz (un rango "rectangular", por ejemplo A2:M10). Para encontrar el elemento buscado debemos incluir uno o dos índices dependiendo se si trata de un vector o una matriz.

En este ejemplo

INDICE crea una referencia a la celda A2, cuyo valor es 40.  En este otro ejemplo

usamos la misma sintaxis a pesar de que el rango es "horizontal". En este caso INDICE crea una referencia a la celda B1.

Podemos concluir que:

• que cuando usamos vectores (rangos unidimensionales) INDICE interpreta el segundo argumento como fila o columna dependiendo de la orientación de vector

• cuando se refiere a un rango, la función INDICE crea una referencia a una celda (o celdas) en el rango.

Cuando usamos INDICE para referirnos a rangos bi-dimensionales, usamos los argumentos de fila y columna para referirnos al elemento buscado en la matriz

En este ejemplo INDICE crea una referencia a la celda B3.

Hasta aquí posiblemente no haya aportado nada dramático a los conocimientos de mis lectores. Ahora los invito a ver esta situación:

Cuando cuando falta el parámetro de fila, o es 0,  INDICE se refiere a toda la columna (en nuestro caso la columna B ya que el parámetro de columna es 2).  Pueden comprobarlo con

34+44+50+26+79+51+57 = 341

De la misma manera, si falta el parámetro columna, INDICE se refiere a toda la fila

47+50+18 = 115

Podemos aprovechar esta propiedad de INDICE para crear rangos dinámicos. La ventaja de usar INDICE en lugar de DESREF o INDIRECTO es que estas dos últimas son volátiles mientras que INDICE no lo es.
Un rango dinámico con INDICE se ve así

=A1:INDICE(A:A,CONTARA(A:A))

donde A1 es la primer celda del rango. Esta fórmula supone que no hay celdas vacías entre A1 y la última celda con valor de la columna A. Para usar esta fórmula como rango dinámica creamos un nombre que se refiera a la fórmula, por ejemplo el nombre "rango_dinamico"

Ahora podemos ver que a medida que agregamos valores en la columna a, el resultado en la celda C1 se va actualizando