BUSCARV CON VALORES REPETIDOS

En este post, vamos a ver como podemos usar la función BUSCARV en la búsqueda de elementos repetidos. de tal forma que podemos cambiar el elemento buscado. Tambíen se podría hacer con filtros avanzados,... pero requeriría usar macros,... 

Con este “truco” que vamos a utilizar, además, no es necesario que el elemento buscado esté en la primera columna del rango.

En el rango B6:E19 tenemos la tabla de la que queremos extraer todos los elementos donde el producto es el especificado en la celda I2 (en esta celda, tenemos un listado con todas los productos obtenido mediante validación de datos).

Si usamos la función BUSCARV en H7, buscando el producto lápices, sólo obtendríamos el primer elemento, el de la fila 8,  porque BUSCARV, cuando existe más de un elemento, sólo devuelve el primero que encuentra ( también tendríamos el problema que  la columna PRODUCTO no está en la primera columna de la tabla, pero se podría salvar copiando esa columna y pegándola a la izquierda del todo, y así estaría en la primera columna ).

Si quisiésemos que nos devolviese todos los elementos, tendríamos que buscar un elemento único, como hemos venido haciendo hasta ahora. 

Para ello, vamos a ayudarnos con la función CONTAR.SI. En A7, escribimos 

=CONTAR.SI($C$6:C7;$I$2),

El rango a evaluar es $C$6:C7, la primera con referencia fija (con $ ) y la segunda sin fijar . De esta forma, el rango, cuando arrastramos hacia abajo, va ampliándose.  En A7 evalúa cuantos sólo C7, en A8 evalúa desde C7 a C8, en A9 evalúa desde C7 a C9,….

Como el criterio es I2 (con referencia absoluta), que es la celda donde aparece el producto, que en el ejemplo es Lápices, cuenta las veces que aparece este elemento en el rango seleccionado.

Así, en la fila 7 aparece un 0, porque el producto no es lápices. En la fila 8 aparece un 1, que es la primera ocurrencia de lápices. . En la fila 9 aparece un 2, porque en el rango C7:C9 hay dos lápices.   Sigue apareciendo un 2 hasta la fila 12, donde aparece un 3, porque es la segunda vez que aparece lápices, y así hasta la fila 12, que es la tercera vez,….

Si cambiamos el producto  de la celda I2, cambian todos los cálculos anteriores.

Ahora, en el rango donde queremos obtener los valores, empezando en G7 escribimos  una columna auxiliar de números empezando por el 1,2,….(que después podremos quitar usando otra función), obteniendo este esquema.

También escribimos una fila auxiliar en H4 comenzando con el 2,3,…

Situándonos ahora en H7, escribimos….

=BUSCARV($G7;$A$6:$E$19;H$4;FALSO)        , donde

-      Valor buscado:       $G7, que es el número 1. Fijamos la columna G con $ para que podamos arrastrar tanto a la derecha ( seguirá buscando G7 como hacia abajo, cuando buscará G8, el número 2.

-      Matriz buscar en:         $A$6:$E$19  Con referencias fijas para que podamos arrastrar. Es la tabla de datos inicial, pero con la característica de que empezamos en A7 y no en B7. En A7 es donde hemos usado anteriormente la función CONTAR.SI para contar las ocurrencias de lápices.   

-    Indicador_columnas.     H$4 Es la fila auxiliar que hemos puesto anteriormente. . Vamos a usar una columna vairable en la búsqueda para que podamos arrastrar.

-    Ordenado.                      FALSO, ya que estamos realizando una búsqueda exacta..

En definitiva , hemos hecho una pequeña  “trampa”  y el valor buscado no va a ser ni el producto, ni el comercial ni ningún dato de la tabla original, si no que el valor buscado va a ser la columna A, que obtuvimos con la función CONTAR.SI.

A partir de la fila 3, obtenemos el error #N/A, porque en la tabla original sólo hay 6 elementos lápices. Para que no aparezca este error, podemos usar la función SI.ERROR

La función SI.ERROR tiene la siguiente sintaxis.

Evalúa un elemento que está definimos en Valor, y en caso de que este sea un error, devuelve el valor que digamos en el otro argumento de la función.

Así, nosotros pondremos

=SI.ERROR(BUSCARV($G7;$A$6:$E$19;H$4;FALSO);””)

Así, evaluamos la función BUSCARV, y si esta es un error, devolverá “” (que es como no poner nada,), aunque también podemos poner un cero o el valor que queramos

El aspecto de la tabla, después de usar la función SI.ERROR es el siguiente

Posteriormente, si queremos eliminar la columna auxiliar G, tendremos que encontrar alguna función que nos devuelva inicialmente 1, y que , conforme arrastremos hacia abajo se convierta en un 2, en un 3,…

Vamos a usar la función FILA, que nos devuelve la fila que ocupa una celda.

Así, la función   FILA(A1), devolverá 1, porque la celda A1 está en la fila 1. La Función FILA(A2), devolverá 2, porque la celda A2 está en la fila 2,… y así sucesivamente.

Si queremos borrar la columna G, escribiremos dentro de la función BUSCARV

=BUSCARV(FILA(a1);$A$6:$E$19;H$4;FALSO

Y arrastraremos hacia abajo. 

Puede que también te interesen estos otros videos sobre BUSCARV.

1.-   Uso básico de la función BUSCARV
2.-     BUSCARV con columna variable.
3.-    BUSCARV con matriz variable o dinámica
4.-    BUSCARV con dos condiciones o criterios
5.-   Gráfico con BUSCARV  con fila y columna variable.