buscar un valor dentro de una matriz bidimensional

Vamos a resolver este planteamiento de dos formas: con y sin macros. La duda planteada se resuelve de forma muy sencilla con una función matricial, la cual recorre uno a uno los elementos de la matriz verificando la existencia de un valor determinado. Ingreso en E1 en valor en cuestión, para luego colocar la siguiente función:

{=O(Matrix=$E$1)}

recordemos que al ser matricial, debemos presionar Ctrol + Shift + Enter al momento de ingresarla. La función O() devuelve verdadero ó falso, según el resultado.

[+/-] Ver el resto / Ocultar

Así las cosas, todo fácil. El tema es que mi lector me solicita una función que especifique en que celda está el valor. Hasta ahora solamente hemos determinado su existencia, pero para llegar a lo requerido queda un largo camino. Empecemos.

Si bien, como digo siempre, en el tema de programación existen muchos caminos distintos, solo pude vislumbrar uno para ayudar a mi lector. Para hacer comprensible la f'órmula final, trabajaremos en un "paso a paso" sobre una matriz bidimensional.

La siguiente imagen muestra una serie de valores, rango A1:A5 (matriz unidimensional). En la celda E1 ingresamos el datos que necesitamos buscar dentro de la matriz. En la columna B comenzaremos a desglosar el problema: con una fórmula pregunto si el valor de E1 se encuentra dentro del rango:

En B1 ingresamos =(A1=$E$1) y lo extendemos hasta B5. Notarán que la tercer posición devuelve VERDADERO, ya que coincide con E1. Noten dentro de la función como hago uso de las referencias relativas y absolutas.

Bueno, el valor está. Ahora en la columna C multiplico la columna B por la FILA() de A:

Todos los valores FALSOS de la columna B devuelven 0 (cero). El valor VERDADERO me devuelve la FILA(A3) que, obviamente, es 3.

Ahora ya sabemos en que posición de la matriz está en valor de E1. Vamos a modificar dicho valor, para que se comprenda mejor el ejemplo:

ahora el resultado es 4, ya que el VERDADERO está en la cuarta posición.

En B7 aplicamos la función MAX() sobre la columna C, devolviéndonos 4, ya que es el maximo valor en dicho rango:

Paso seguido, hacemos uso de la función:

=DIRECCION(fila, columna, [abs])

Sintaxis: el primer y segundo argumento son sencillos, solo debemos especificar el nro de fila y columna de la celda sobre la cual deseamos obtener su dirección. El parámetro opcional ABS indicará la forma en que Excel nos devolverá esa dirección: relativa o absoluta. Admite numeros del 1 al 4:

los dos primeros argumentos son iguales en las 4 funciones (fila 1, columna 1 = celda A1). Pero al cambiar el tercer argumento apareceren las diferencias entre las direcciones relativas y absolutas.

Ahora si resolvimos el problema. Sabemos que nuestro valor está en la cuarta fila, de la columna 1, por lo tanto en B8 ingresamos la función final:

Si bien en B8 ya tenemos la dirección de la celda en la que se encuentra el valor ingresado en E1.... hemos utilizado dos columnas auxiliares + una celda auxiliar. Y no es que esté mal, pero todo se puede resumir a una sola función.
Sigamos: aqui logramos como resultado un 4, ya que continúo con el mismo ejemplo. Comparo A1:A5 con el valor de $E$1 y lo multiplico por su respectiva fila. Lo mismo que hice antes en la Columna C, pero esta vez dentro de una función matricial:

{=MAX(((A1:A5)=$E$1)*FILA(A1:A5))}

 A la función anterior le agregamos DIRECCION() y tenemos todo resuelto dentro de D3:

{=DIRECCION(MAX(((A1:A5)=$D$2)*FILA(A1:A5));1)}

Vimos detalladamente como logramos identificar el nro de fila. Para hacer lo propio con la columna, la lógica es igual, como muestro en el siguiente ejemplo:

ya tenemos en nuestras manos al numero fila y columna del valor ingresado en B5

Solo nos resta anidar estas funciones dentro de DIRECCION(), en B6

{=DIRECCION(MAX((($A$1:$C$3)=$B$5)*(FILA($A1:$C3)));MAX((($A$1:$C$3)=$B$5)*(COLUMNA(A$1:C$1))))}

Siempre recuerden que para ingresar una función matricial debemos presionar Ctrol + Shift + Enter, lo que encerrará las f'órmulas entre las llaves { }

Para acortar un poco la longitud de la función resultante, podemos darles nombres a los rangos A1:C3 y B5:

{=DIRECCION(MAX(((Matrix)=V)*(FILA(Matrix)));MAX(((Matrix)=V)*(COLUMNA(Matrix))))}

Esto da el nombre a A1:C3 como "Matrix" y a B5 como "V"

Si ingresamos un valor inexistente, la función devolverá error

Lo podemos controlar mediante el Formato Condicional. Seleccionamos la celda con el error y hacemos esto:

=ESERROR(B6) retornará VERDADERO si hay en error en dicha celda, entonces aplicará el formato que le especifiquemos: en este caso, las letras de color negro, para que se confunda con el fondo de la celda y no se visualice nada.

Prueben con el "paso a paso" y el ejemplo que les dejo en este link.

Las funciones matriciales son una poderosa herramienta, de gran ayuda en nuestro trabajo diario.