Búsqueda doble sobre cualquier parte de una tabla de Excel.

Aunque parece un tema más que trillado, en el ejercicio de hoy iremos un paso más allá...
Buscaremos un elemento sobre cualquier parte de una tabla o rango de celdas y retornaremos el valor correspondiente a esa fila que deseemos.

...Necesito generar una fórmula similar al buscarv, pero que tenga los siguientes argumentos:
=buscador(A6:F10,"Miranda","nombre")
Es decir, la macro debe analizar toda la base de datos hasta encontrar a "Miranda" y arrojar como resultado su valor equivalente de la columna "nombre":

la idea es, por tanto, clara.. buscaremos en la totalidad de nuestra tabla el valor buscado (que podrá estar en cualquier fila o columna!!), y una vez localizado, obtendremos su equivalente al nombre de campo indicado.

Como se aprecia en la imagen en la celda H6 hemos añadido una Validación de datos tipo Lista en la que desplegaremos el nombre del campo del que queremos obtener el resultado.
Mientras que en I6 escribiremos cualquier valor único (en caso de duplicados fallaría nuestra fórmula) que realmente queremos localizar en cualquier parte de nuestra tabla (rango A6:F10):

Búsqueda doble sobre cualquier parte de una tabla de Excel.

haz clic en la imagen

La fórmula matricial deseada aparece en J6:
=INDIRECTO(DIRECCION(K.ESIMO.MAYOR(SI(($I$6=$A$7:$F$10)*1=1;FILA(A7:F10);0);1);COINCIDIR($H$6;$A$6:$F$6;0)))

Fijémonos de la potencia de esta fórmula.. ya que con ella no necesito indicar en qué columna debe encontrar nuestro valor buscado (celda I6), puesto que lo busca en toda la tabla... para localizar la fila donde se halla.
Posteriormente, aprovechándonos de la Validación de datos en H6 y de la ya conocida función COINCIDIR obtenemos la celda con el resultado final.

La clave de esta matricial reside en el primer argumento de la función DIRECCION:
K.ESIMO.MAYOR(SI(($I$6=$A$7:$F$10)*1=1;FILA(A7:F10);0);1)
donde con el condicional SI:
SI(($I$6=$A$7:$F$10)*1=1;FILA(A7:F10);0)
obtendríamos una matriz de ceros y un número de fila, para el caso o celda donde se de la coincidencia con el valor buscado. Forzamos que el condicional nos devuelva el número de la fila en cuestión.
Podemos ver, a parte, el resultado de esa matriz:

Lo siguiente es sencillo.. basta aplicar la función K.ESIMO.MAYOR (xxxx; 1) para conseguir el valor más alto (el número de fila buscado) -también valdría la función MAX-.

Con esto y daría respuesta a la lectora del blog, sin emplear macros...
De todas formas, en una entrada posterior, daré el código para resolver esto con una función VBA personalizada.

6 comentarios:

Unknownnoviembre 13, 2014
Este comentario ha sido eliminado por el autor.
ResponderEliminar
Respuestas
Unknownnoviembre 13, 2014
Hola Ismael,
Gracias por la opción! Pero estoy necesitando una codificación. Por el momento he trabajado la siguiente, pero esta "función" necesita de 4 argumentos,
Function MIBUSCADOR(RANGO As Range, TITULO1, DATO1, TITULO2)
FILAS = RANGO.Rows.Count
COLUMNAS = RANGO.Columns.Count
For Y = 1 To COLUMNAS
If RANGO.Cells(1, Y) = TITULO1 Then 'filas,columna
COLUMNADATO = Y
End If
Next
For X = 2 To FILAS
If RANGO.Cells(X, COLUMNADATO) = DATO1 Then
FILADATO = X
End If
Next
For Y = 1 To COLUMNAS
If RANGO.Cells(1, Y) = TITULO2 Then
COLUMNARESPUESTA = Y
End If
Next
MIBUSCADOR = RANGO.Cells(FILADATO, COLUMNARESPUESTA)
End Function

Yo quiero saber si es posible que mi macro busque en la totalidad de la matriz el valor que le digo, en este caso con solo tres argumentos algo asi, =mibuscador(matriz,"Miranda", "nombre") es decir busque el apellido "Miranda" tal cual, y bote el resultado de la columna que le indique, en este ejemplo "nombre".
Gracias!!!!
ResponderEliminar
Respuestas
Andrésnoviembre 13, 2014
Hola, Ismael. Antes he introducido un comentario pero no sé si lo he hecho bien, porque no aparece (aunque quizá sea porque haces un control previo de comentarios; si es así, como es lógico, no hace falta que publiques o atiendas a éste). En fin, por si acaso, lo vuelvo a intentar, marcando esta vez la casilla Avisarme.
La cosa es lo siguiente: en el primer argumento de la función SI, ¿para que sirve multiplicar por 1 y hacer la igualdad con 1? Lo he probado sin eso y también funciona. Seguramente hay una razón pero se me escapa.
En fin, muchas felicidades por tan estupendo blog y gracias por anticipado.
ResponderEliminar
Respuestas