lunes, 17 de noviembre de 2014

Búsqueda vertical concatenada.

Daré respuesta a una compañera de un grupo de LinkedIn al que pertenezco, en el que planteaba una alternativa a una búsqueda sobre dos columnas.
...Tengo el siguiente planteamiento:
En la primera hoja tengo dos códigos en dos columnas diferentes, y necesito localizar esa pareja de dos códigos en otra hoja y si existe obtener la fecha que está en otra columna de esa segunda hoja.
La cuestión es se os ocurre cómo hacerlo SIN MACROS?
Lo he intentado con un SI, con Y, y con COINCIDIR pero en el momento que se encuentra primero un código en una fila y el otro código en otra fila distinta ya no me da el resultado...

Imaginemos el planteamiento de inicio siguiente:

Búsqueda vertical concatenada.



El tema es claro, debo encontrar y mostrar en F1:F10 las fechas correspondientes al par de valores compuesto por las columnas A1:A10 y B1:B10... sin columnas auxiliares

Para un caso como este ofrezco dos alternativas: una matricial y otra estándar.

Vamos con la matricial.
En F1 escribiríamos:
=VALOR(DERECHA(BUSCARV(E1;$A$1:$A$10&$B$1:$C$10;2;0);LARGO(BUSCARV(E1;$A$1:$A$10&$B$1:$C$10;2;0))-1))
y ejecutamos presionando Ctrl+Mayusc+Enter, luego copiamos y pegamos al resto del rango.

La clave del asunto es cómo hemos construido la matriz de búsqueda:
$A$1:$A$10&$B$1:$C$10
que como vemos en la imagen siguiente nos concatena la primera columna, una a una, con la dos siguientes:

Búsqueda vertical concatenada.



El resto de la fórmula es fácil de entender, aplicamos la función BUSCARV que encuentra en nuestra primera columna 'virtual' el código completo, retornándonos el valor correspondiente de la segunda columna, compuesto de un caracter y la fecha (precisamente lo que queremos conseguir).
De ahí que ese valor devuelto lo tratemos con la función de texto DERECHA y acabemos anidando el resultado parcial con la función VALOR, para convertirlo en número y a través del formato en fecha.


La otra forma que mencionaba al principio, se basa en la hipótesis que no hay dos combinaciones iguales, esto es, que son códigos únicos (al igual que en el planteamiento anterior).
La fórmula buscada podría ser la conocida SUMAR.SI.CONJUNTO:
=SUMAR.SI.CONJUNTO($C$1:$C$10;$A$1:$A$10;IZQUIERDA(E1;1);$B$1:$B$10;DERECHA(E1;LARGO(E1)-1))
en la que descomponemos el código completo para buscar la combinación en el rango origen...


Por supuesto hay más formas, por ejemplo empleando nuestros recurridos INDICE y COINCIDIR:
=INDICE($C$1:$C$10;COINCIDIR(E1;$A$1:$A$10&$B$1:$B$10;0))

3 comentarios:

  1. Buenas estimado,
    Tengo el siguiente problema, debo realizar una busqueda en diferentes libros, según el mes y el año.
    Ej. Si es el mes 1 y el año es 2014, debo buscar en el archivo del año 2014 y asi sucesivamente.
    He tratado de aplicar esta formula en el Buscarv en la matriz de busqueda utilizando un concatenado con el ampersem. Pero me presenta cada vez que se quiere modificar la venta de actualizar valores.
    En las celdas esta la información E1=2014, F1=2015 y G1=2016
    =SI(Y(MES(B3)=1;AÑO(B3)=2014);BUSCARV(A4;'D:\REPOSOS\[Plantilla Activa al cierre Enero&$E$1&.xlsx]BASE CEDULA'!$A$1:$G$3000;7;0);SI(Y(MES(B3)=1;AÑO(B3)=2015);BUSCARV(A4;'D:\REPOSOS\[Plantilla Activa al cierre Enero&$F$1&.xlsx]BASE CEDULA'!$A$1:$G$3000;7;0);SI(Y(MES(B3)=1;AÑO(B3)=2016);BUSCARV(A4;'D:\REPOSOS\[Plantilla Activa al cierre Enero&$G$1&.xlsx]BASE CEDULA'!$A$1:$G$3000;7;0);"NO")))

    Será que si estoy aplicando bien el uso del ampersem.

    Agracediendole de ante manos su ayuda, quedo a la espera de su respuestas.
    Mi correo: tepuyes01@gmail.com
    Edgar González
    Caracas-Venezuela

    ResponderEliminar
    Respuestas
    1. Hola Edgar,
      habría que conocer exactamente los nombres de los ficheros donde está la información, pero la idea podría ser, si en la celda E1 está el año de la búsqueda:
      BUSCARV(A4;INDIRECTO("'D:\REPOSOS\[Plantilla Activa al cierre Enero"&$E$1&".xlsx]BASE CEDULA'!$A$1:$G$3000");7;0)
      Saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.