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
