BUSCARV en varias hojas de Excel. Regalo: UDF BuscarvMix

Como todos saben, la función BUSCARV es mis funciones favoritas. Todo el tiempo la uso. En estos días he tenido la necesidad de tener que buscar valores en varias hojas de Excel, y cada vez son más hojas en las que tengo buscar valores. Dicho lo anterior, he tenido que anidar varias funciones BUSCARV para buscar en varias hojas.

BUSCARV anidado con función SI.ND

Como lo comenté en el primer párrafo, para buscar valores he tenido que anidar varias veces la función BUSCARV, combinándola con la función SI.ND. Recordemos que la función SI.ND nos detecta cuando una fórmula devuelve el valor #N/A, en otras palabras, queda perfecta para la función BUSCARV. Si bien la función SI.ERROR también detecta el error #N/A, también detecta el resto de errores que puede devolver una fórmula en Excel.

Figura 1. BUSCARV en varias hojas de Excel.

En la Figura 1 vemos que debemos hacer la búsqueda en 5 hojas de Excel que contienen países por continente y sus respectivas capitales. En la hoja BUSCARV deberemos introducir la fórmula donde anidaremos 5 funciones, obvio de BUSCARV.

Ver el video BUSCARV en varias hojas de Excel

Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.

Primero, probando con dos BUSCARV y un SI.ND

Para entender cómo podemos combinar la función SI.ND con BUSCARV haremos una primera prueba donde buscaremos sólo en dos hojas. La fórmula quedaría así:

=SI.ND(BUSCARV(B9,AMÉRICA!B1:C36,2,0),BUSCARV(B9,EUROPA!B1:C47,2,0))

Para describir la fórmula anterior podemos decir que “Si la primer función BUSCARV devuelve el valor #N/A entonces hacemos otra función BUSCARV para buscar en otra hoja”.

Figura 2. BUSCARV combinado con SI.ND.

Buscar valores en 5 hojas

En la Figura 2 vimos cómo anidar dos BUSCARV y combinando la función SI.ND, Ahora les mostraré la fórmula para hacer la búsqueda en las 5 hojas que tenemos. Al igual que el otro ejemplo, con la función SI.ND le estaremos diciendo que mientras el resultado sea #N/A siga aplicando el BUSCARV hasta la última fila.

=SI.ND(BUSCARV(B9,AMÉRICA!$B$1:$C$36,2,0),SI.ND(BUSCARV(B9,EUROPA!$B$1:$C$47,2,0),SI.ND(BUSCARV(B9,ASIA!$B$1:$C$48,2,0),SI.ND(BUSCARV(B9,AFRICA!$B$1:$C$55,2,0),BUSCARV(B9,OCEANIA!$B$1:$C$16,2,0)))))

Como vemos, entre más hojas sean donde buscaremos, más larga se hace la fórmula.

Figura 3. Anidando funciones BUSCARV.

¿Muchas hojas donde buscar? Crea una UDF en Excel.

Ahora bien, qué sucede cuanto tenemos que buscar en una cantidad considerable de hojas? Tal vez lo más viable sea auxiliarnos de las macros para hacer la labor más rápida y automatizada. Para lo anterior, desarrollé una función UDF, Función Definida por el Usuario, por sus siglas en inglés, llamada BuscarvMix. Dicha función tendrá los siguientes parámetros:

valor_buscado: Es el valor que se buscará en todas las hojas hacia la derecha.

primer_columna: Es la columna donde se encuentra el valor buscado.

devolver: Es la columna donde estará el valor que deseamos devolver como resultado de la función.

Nota: Es importante que todas las hojas tengan datos con la misma estructura, por lo menos desde la columna primer_columna hasta la columna devolver.

Figura 4. Parámetros de la UDF BuscarvMix.

Código vba de la función UDF

Para usar la función deber abrir el editor de vba con la combinación de teclas [Ctrl] + [F11] y en un módulo normal pegar el siguiente código vba.

Option Explicit

'EXCELeINFO
'MVP Sergio Alejandro Campos
'http://www.exceleinfo.com
'https://www.youtube.com/user/sergioacamposh

Function BuscarvMix(valor_buscado As Range, PrimerColumna As Integer, Devolver As Integer)
'Declaramos variables
Dim i As Integer
Dim Alto As Integer
Dim Rango
Dim Valor As String
Dim Valor2 As String

'Definimos la aplicación como Volatile, es decir, que se calcule automáticamente
Application.Volatile
On Error Resume Next
'Recorremos cada una de las hojas del libro
For i = 2 To ActiveWorkbook.Sheets.Count
    'Definimos el alto del rango de cada una de las hojas al número de filas con datos.
    Alto = Application.WorksheetFunction.CountA(Sheets(i).Columns(PrimerColumna).EntireColumn)
    'Definimos cada matriz de búsqueda de cada una de las hojas
    Rango = Range(Cells(1, PrimerColumna), Cells(Alto, PrimerColumna + Devolver - 1)).Address
    'A la variable Valor le asignamos lo que devuelva la función BUSCARV (VLookup)
    Valor = Application.WorksheetFunction.VLookup(valor_buscado.Value, Sheets(i).Range(Rango), Devolver, 0)
    'Si la función BUSCARV devuelve un valor, entonces se detiene el ciclo For
    If Valor <> Empty Then Exit For
Next i
On Error GoTo 0
'Si el BUSCARV devuelve un valor vació, entonces mostramos el valor #N/A
If Valor = Empty Then
    BuscarvMix = VBA.CVErr(xlErrNA)
Else
    'Si BUSCARV devuelve un valor, lo asignamos como valor final a la función BuscarvMix
    BuscarvMix = Valor
End If

End Function

Descarga el archivo de ejemplo

Fe de erratas: El archivo no contenía la sentencia que omite los errores para seguir buscando en el resto de las hojas.

 BUSCARV en varias hojas – EXCELeINFO.rar

Seguro te puede interesar

El poder de BUSCARV.

Aprendamos UDF’s en Excel.